Re: snowflake schema and index

Stefan Seidel wrote:

>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
>>
>>

Have you missed the comments we make in this n.g. that posts this question?

Did you collect statistics?
Did you run an EXPLAIN PLAN?
Did you do any of the things required to identify the underlying cause?

And why are you doing anything in 9.0.1? I can't think of any possible excuse. Upgrade to 9.2.02+ immediately.
That alone will get you 50% of the way to where you want to go.

-- 
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)