Views

 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. Received on Wed Dec 15 1999 - 10:09:31 CST