You did gather statistics on these tables and if so what does the
query plan look like. I have a fair idea what's going on but a query
plan of both would be the easy way to tell, you also may want to try
the oracle groups.

"Stefan Seidel" <sseidel (AT) uni-muenster (DOT) de> wrote

Hi NG,

i created a snowflake schema (one fact table and denormalized dimension
tables for one dimension) in oracle 9.0.1.

if i do NOT set and index, a rollup over all dimension levels is much faster
than if i set and index on the primary key in the fact table (which is a
foreign key to the dimension tables) and on the foreign keys of the
dimension levels.


dimension tables:
CREATE TABLE dimension_1(dim_id INTEGER PRIMARY KEY);
CREATE TABLE dimension_2(dim_id INTEGER PRIMARY KEY, parent INTEGER
REFERENCES dimension_1);
CREATE INDEX dimension_2_Index ON dimension_2(parent);

fact table:
CREATE TABLE snowflake_fact(dim_id INTEGER, value INTEGER);
CREATE INDEX fact_Index ON snowflake_fact (dim_id);


how come that?

thanx,
Stefan

Sorry, i mean normalized dimension tables, of course.
Stefan

"Stefan Seidel" <sseidel (AT) uni-muenster (DOT) de> wrote

so here the information! Thanx for any help!

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.snowfl ake_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

"Niko" <nick_wakefield (AT) hotmail (DOT) com> wrote