Ben Ziegler wrote:
V_VIEW looks like sort of like this:
select ID, A, B, t2.C, sum(t2.D)
from table1 t1
left join table2 t2 on t1.ID = t2.ID
group by ID, A, B, C
When I run a query like this:
select * from V_VIEW where ID = X
it is SLOW. But if I don't use the view, and just take the SQL that makes up V_VIEW and put the "where ID = X" into that SQL, it is FAST.
Ann W. Harrison answers:
The problem is that the optimizer (actually the semantic analysis pass of the compiler) doesn't reorganize queries that include views with group by, distinct, or aggregates. Other views are expanded and integrated with the query that references them, before optimization so they have little or no performance impact. For example, if your view didn't have the group by and sum clauses this statement:
select * from V_VIEW where ID = X
would be converted to
select t1.ID, t1.A, t1.B, t2.C
from table1 t1
left join table2 t2 on t1.ID = t2.ID
where t1.ID = 'X'
Then the optimizer would recognize that there is a selective index on table1 (ID). The plan would be an indexed lookup on table1 ID and a join from there to table2 on its ID index. Very quick.
Complex views - those with group by, distinct, or aggregates - are handled as a separate record stream and fully evaluated before being integrated with the query.
So, what's happening is that the whole join on both tables is materialized, grouped, and summed, then all the records with a value of t1.ID other than 'X' are dropped on the floor. Very inefficient.
The good news is that V2 has a lot of improvements in semantic analysis and should do a better job with this type of view.