Re: Views , unions and performance

>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, orta b where a.c1 = b.c1 and a.c2 = b c2; create view v2 (c1, c2, c3 .... )as select .... from t2 a, orta 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