Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: snowflake schema and index
Sorry, i mean normalized dimension tables, of course.
Stefan
"Stefan Seidel" <sseidel_at_uni-muenster.de> wrote in message
news:bkejnv$jqk$1_at_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 - 14:12:04 CDT