Re: snowflake schema and index

Stefan Seidel wrote:

>Sorry Morgan, you are right of course. I am new to database development and
>til yesterday i did not even know there is an explain plan...
>
>so here the information:
>
>when i set the indexes as shown below i get a full access on the table
>containing the attribute i use in the group by part. if i do not set and
>indexes, i get a full acces on the fact table and only on that.
>
>statement:
>(SELECT snowflake_dim_4.id, sum(wert)FROM dwh.snowflake_fakten,
>dwh.snowflake_dim_0,dwh.snowflake_dim_1,dwh.snowflake_dim_2,dwh.snowflake_di
>m_3,dwh.snowflake_dim_4 WHERE snowflake_fakten.dim_id = snowflake_dim_0.id
>AND snowflake_dim_0.ueber_id = snowflake_dim_1.id AND
>snowflake_dim_1.ueber_id = snowflake_dim_2.id AND snowflake_dim_2.ueber_id =
>snowflake_dim_3.id AND snowflake_dim_3.ueber_id = snowflake_dim_4.id GROUP
>BY snowflake_dim_4.id);
>
>CREATE TABLE snowflake_dim_5(ID INTEGER PRIMARY KEY)
>CREATE TABLE snowflake_dim_4(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_5(ID))
>CREATE INDEX snowflake_dim_4_Index ON snowflake_dim_4(UEBER_ID)
>CREATE TABLE snowflake_dim_3(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_4(ID))
>CREATE INDEX snowflake_dim_3_Index ON snowflake_dim_3(UEBER_ID)
>CREATE TABLE snowflake_dim_2(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_3(ID))
>CREATE INDEX snowflake_dim_2_Index ON snowflake_dim_2(UEBER_ID)
>CREATE TABLE snowflake_dim_1(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_2(ID))
>CREATE INDEX snowflake_dim_1_Index ON snowflake_dim_1(UEBER_ID)
>CREATE TABLE snowflake_dim_0(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_1(ID))
>CREATE INDEX snowflake_dim_0_Index ON snowflake_dim_0(UEBER_ID)
>CREATE TABLE star_fakten(DIM_ID INTEGER REFERENCES star_dim(dim_0_id), WERT
>INTEGER)
>CREATE INDEX snowflake_fakten_Index ON snowflake_fakten (DIM_ID)
>
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (GROUP BY)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_FAKTEN'
> 3 2 NESTED LOOPS
> 4 3 NESTED LOOPS
> 5 4 NESTED LOOPS
> 6 5 NESTED LOOPS
> 7 6 NESTED LOOPS
> 8 7 TABLE ACCESS (FULL) OF 'SNOWFLAKE_DIM_4'
> 9 7 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_
> DIM_3'
>
> 10 9 INDEX (RANGE SCAN) OF 'SNOWFLAKE_DIM_3_IND
> EX' (NON-UNIQUE)
>
> 11 6 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DI
> M_2'
>
> 12 11 INDEX (RANGE SCAN) OF 'SNOWFLAKE_DIM_2_INDEX
> ' (NON-UNIQUE)
>
> 13 5 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DIM_
> 1'
>
> 14 13 INDEX (RANGE SCAN) OF 'SNOWFLAKE_DIM_1_INDEX'
> (NON-UNIQUE)
>
> 15 4 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DIM_0'
> 16 15 INDEX (RANGE SCAN) OF 'SNOWFLAKE_DIM_0_INDEX' (N
> ON-UNIQUE)
>
> 17 3 INDEX (RANGE SCAN) OF 'SNOWFLAKE_FAKTEN_INDEX' (NON-
> UNIQUE)
>
>
>without indexes:
>
>CREATE TABLE snowflake_dim_5(ID INTEGER PRIMARY KEY)
>CREATE TABLE snowflake_dim_4(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_5(ID))
>CREATE TABLE snowflake_dim_3(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_4(ID))
>CREATE TABLE snowflake_dim_2(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_3(ID))
>CREATE TABLE snowflake_dim_1(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_2(ID))
>CREATE TABLE snowflake_dim_0(ID INTEGER PRIMARY KEY,UEBER_ID INTEGER
>REFERENCES snowflake_dim_1(ID))
>CREATE TABLE snowflake_fakten(DIM_ID INTEGER REFERENCES snowflake_dim_0(ID),
>WERT INTEGER)
>
>
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (GROUP BY)
> 2 1 NESTED LOOPS
> 3 2 NESTED LOOPS
> 4 3 NESTED LOOPS
> 5 4 NESTED LOOPS
> 6 5 NESTED LOOPS
> 7 6 TABLE ACCESS (FULL) OF 'SNOWFLAKE_FAKTEN'
> 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DI
> M_0'
>
> 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C006699' (UNIQUE
> )
>
> 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DIM_
> 1'
>
> 11 10 INDEX (UNIQUE SCAN) OF 'SYS_C006697' (UNIQUE)
> 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DIM_2'
> 13 12 INDEX (UNIQUE SCAN) OF 'SYS_C006695' (UNIQUE)
> 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SNOWFLAKE_DIM_3'
> 15 14 INDEX (UNIQUE SCAN) OF 'SYS_C006693' (UNIQUE)
> 16 2 INDEX (UNIQUE SCAN) OF 'SYS_C006691' (UNIQUE)
>
>
>Thanx for the help.
>Stefan
>
>
>

Can't remember your original question but go to tahiti again and look up the following:

1. Hints look for the STAR hint.

The STAR hint forces a star query plan to be used if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least 3 tables, the large table's concatenated index has at least 3 columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

2. The init.ora parameter: STAR_TRANSFORMATION_ENABLED.

Also, with the Cost Based Optimizer be sure your statisitics are current with a sample size sufficient for accuracy.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)