Re: Views , unions and performance

In article <19981112080904.25504.00000570@ng147.aol.com>, satriguy@aol.com (SaTriGuy) wrote: > >Hi all, > >We have a view with a union of 2 indexed large tables. We want to make a join > >of the view with a little table by the indexed fields, but first it makes 2 > >seq. scan to create the view and after it makes the join. We think this's not > >the best way. We think the Optimizer is not using view info in to order to > >do > >the query plan. > >We're running over a DEC-Alpha (Unix) an IDS 7.30FC4' > >------- > >create view vista (c1,c2,...)> >as > > select * from t1 > > union > > select * from t2 > >--------> >t1 and t2 are indexed by c1 and c2 > >------------------ > >create table otra (c1,c2,...) > >--------------------> >Select v.* from vista v, otra t where v.c1=t.c1 and v.c2=t.c2 > >---------------------- > >results: > >(Temp Table for view) SEQUENTIAL SCAN > > otra :SEQUENTIAL SCAN > >DYNAMIC HASH JOIN > > > >Thanks in advance > > > > Please, mail to: > > e-mail: pablof@my-dejanews.com > > > > I don't have my documentation with me right now, but if memory serves me right, > a union returns only unique rows. That would mean that the best way to produce > the view would be to produce the temp table which would be the result of a sort > in which the sort was used to eliminate the duplicate rows. > > Since the view is going to result in a dynamically created tempory table, there > will be no indexes, so the join can not make advantage of those tables. > > I think that in this case, if the procedure is reversed, (i.e. create the join > and then the union), your performance would be greatly improved. > Maybe if you had two views defined as: > > create view v1 (c1, c2, c3 ..... )> as select ..... from t1 a, otra b where a.c1 = b.c1 and a.c2 = b c2; > > create view v2 (c1, c2, c3 .... )> as select .... from t2 a, otra b, where a.c1 = b.c1 > and a.c2 = b .c2; > > and then in your program .... > > select * from v1 > union > select * from v2;> > I suspect this is one of the rare cases where the order that work is done will > make a significant difference. But the reason for this is not so much the > selection criteria, but the union. > Madison Pruet > Hi Madison, Thank you for your answer. I want to remark a few things. The two tables involved in query do not have any row in common reason why the results are unique respect this index. On the other hand, I cannot create the view after to make the join, because this view is a user requirement. Now, in operation database, the view is really a unique table. After a study, we saw that users access frequently a part of the table, and the performance is very bad. So, we think to make a fragmentation in two tables, but whithout modifying the programs. The best result of all this is a view, and before this operation we're studying pros and cons. Then, we detected this problem. We think that the optimizer must be able to reach the same solution that you propose by its own means. Thank you. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own