Cost based analyser and views

From: Volker Apelt <gq437x_at_yahoo.de>
Date: 07 Nov 2002 17:19:14 +0100
Message-ID: <lg65v9cpz1.fsf@mu.biosolveit.local>

Will the query plan for a select containing a view under go further optimization by the Cost based analyser?

Will the CBO take advantage of WHERE clauses on fields of tables contained inside the view?

How is will it change if I use a materialized view?

##Example:
I have 5 tables A through A .. E

'A' is linked to some tables outside this schema.  B through E reference A direct or indirect.

 A -|----0< B -|----|< C >|----|- D

>|----|- E

Each has an ID field named ID_<name_of_table>

##row counts are about:

A   ~ 1.000.000
B   ~ 1.000.000 * 300
C   ~ 1.000.000 * 300 * 5000
D   ~ 1.000.000 * 300 * 50
E   ~ 1.000.000 * 300 * 60

## Additional fields  
A.group             can have about  200 different values
A.no                can have about 5000 different values
B.no                can have about  300 different values
D.field1            can have about   50 different values.

There is one index for each of those fields.

##The view is:

create view v_across
as select
  A.ID_A, B.ID_B, C.ID_C, C.ID_D, C.ID_D from
  A, B, C, D, E
where
  A.ID_A = B.ID_A and
  B.ID_C = C.ID_C;

###############

Will this select use the indices on A.group, A.no ... ?

select
  A.group, A.no, B.no, d.field1, .. some other fields from
  v_across v,

  D        d,
  B        b

where

  A.group = 3  and 
  A.no    = 25 and 
  B.no    < 5  and
  D.field1 in ('x','y','z');
     

Thank you,

Volker

-- 
Volker Apelt                   

Received on Thu Nov 07 2002 - 10:19:14 CST