Re: Unknown SQL

In article <u7wv6zvsxf.fsf_at_sol6.ebi.ac.uk>, Philip Lijnzaad says...
>
>On Wed, 30 May 2001 13:56:39 +0200,
>"Carl" == Carl Rosenberger <carl_at_db4o.com> wrote:
>
>Carl> It seemed that views where evaluated and retrieved completely before
>Carl> constraining them by other parts of the query.
>
>which I still find hard to believe; I would consider a database that
>implements queries on views this way, broken (that is, in the general case;
>in certain queries it may of course be decided that this is optimal).
>
>Carl> I am not informed about the current state of development. I do know
>Carl> that MSSQL introduced indexed views some time ago. From reading
>Carl> newsgroups I get the idea that views still are very slow.
>
>I still disagree with this vehemently, at least for Oracle because that's the
>only database I have access too. In all cases, views performed as well as
>fully expanded queries on base tables.
>
You are both correct (or wrong;-). View merging merges the body of the view to the body of the query. Optimizer then consideres the merge as a single query. This allows the optimizer to consider efficient join orders and index access paths. However, not every view is mergeable. What views are not mergeable in Oracle8, for example?

- outer joins to views containing correlated subqueries
- outer joins to views containing column concatenations
- aggregates
- union, minus, intesect
- group by
- distinct