Re: snowflake schema and index

From: Stefan Seidel <sseidel_at_uni-muenster.de>
Date: Sat, 20 Sep 2003 12:55:04 -0700
Message-ID: <bkhbn3$qtc$1@redenix.uni-muenster.de>

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 Received on Sat Sep 20 2003 - 14:55:04 CDT