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

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.

Regards,

Ann