Re: Views

Isabelle, the only way to be sure how Oracle is going about solving your query is to either run an explain on your SQL or to run tkprof on a trace from your SQL.

In general it is more efficient to write a query that goes against only those tables that contain data you are interested in than to use an outer join against a whole series of tables that might have data that you do not want to see anyway.

Views are often used to hide complexity from users. That complexity is often a join or series of joins. Your views are probably just for that purpose and how effecient they are depends on weather Oracle is:

1. merging the view with you SQL
2. merging your SQl to the view
3. firing the view and executing your SQL against the result set of the view like it is a table

The easiest way to judge the views may be just to run and time SQl against them.

In article <838efm$5sq$1_at_nnrp1.deja.com>,   Isabelle <isafz_at_my-deja.com> wrote:
> 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"
>
> (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 ?
>
> 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 ?
>
> Thanks !
>
> Isabelle.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 14:12:22 CST