snowflake schema and index

From: Stefan Seidel <sseidel_at_uni-muenster.de>
Date: Fri, 19 Sep 2003 11:54:03 -0700
Message-ID: <bkejnv$jqk$1@redenix.uni-muenster.de>

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 Received on Fri Sep 19 2003 - 13:54:03 CDT