Re: Views

Isabelle <isafz_at_my-deja.com> wrote in message news:838efm$5sq$1_at_nnrp1.deja.com...
> Hi !
>
> A beginner question, but I really need to be sure...
>
>
> We have N tables which contain each from 1,5 million rows for the
> smallest to 35 million rows for the biggest.
>
> Each table has a primary key named Oid.
>
> At one time, one guy here has created the following views:
>
> create view V1 as
> select T1.Oid T1Oid, x1, x2, x3, ... from T1, T2, T3, Tm
> where (T1.Oid = T2.x2nOid) and (T1.Oid=T3.x3nOid) and ....;
>
> create view V2 as
> select T1.Oid T1Oid, y1, y2, y3, ... from T1, T5, T9, Tp
> where (T1.Oid = T5.x5nOid) and (T5.Oid=T9.x9nOid) and ....;
>
> ...
>
> create view VM as
> select T1.Oid T1Oid, z1, z2, z3, ... from T1, T8, T30, Tm
> where (T1.Oid = T30.x30nOid) and (T30.Oid=T8.x3nOid) and ....;
>
> Well, a great number of complex views, each selecting several infos in
> different tables, joined through Primary keys/foreign keys or at least
> selected via indexed fields.
>
> At the end, in order to select data (something like 60 fields), the guy
> has created a single view which groups all the others :
>
> create view TheBigView as
> select V1.T1Oid, V1.x1, ... V2.y1, V2.y2, ... VM.z1, VM.z2, ....
> from V1, V2, ... VM
> where (V1.T1Oid = V2.T1Oid (+)) and
> (V1.T1Oid = V3.T1Oid (+)) and ....
> (V1.T1Oid = VM.T1Oid (+));
>
> My question is quite simple : If I do the following select :
>
> "select * from TheBigView where Vn.f10 = 567"

The column name will not be "Vn.f10".

> (Vn.f10 is an indexed field in the origin table), is it longer than
> using each basic view to retrieve the 60 fields that are interesting
> me ?

Maybe, or maybe not.
We don't see the whole, no one can make sure.

> Is the first level of view (V1, V2, .. VM) using indexes defined on
> tables ? I think yes. Am I wrong ?
>
> Is TheBigView using indexes defined on the basis tables ?

Oracle will try to merge the definition of views into the query, and try to use indexes if they are applicable.

Your "TheBigView" may NOT contain all the Oids in T1. Your query through the view and your query through the table may return different results.

So, you should ask yourself first what's the correct result you want. Received on Fri Dec 17 1999 - 09:35:49 CST