Re: Star query/dimensional modelling help ?

Check out Ralph Kimball's *Data Warehouse Toolkit* book. If you change your dimensional structure to his format you'll get far better performance.

If you take Kimball's format and add a dimension table for each level, you will have the fastest possible star schema.

Using this set-up, I experienced the fastest queries in Oracle 7.3 by turning off the indexes. (I'm hesitant to post this since people with traditional schemas will think I'm nuts. Presumably this is due to a large SGA and hash joins, although I haven't confirmed.)

If you are doing a star schema to support Oracle Express, then a combination of bitmapped, b-tree and no indexes is needed. The RAM manual is good to follow. (Note that if you turn off indexes in a schema that Express supports, your queries will be running for a long long time.)

I would forget trying to get Oracle 7.3 to do a star join. The star join algorithm in Oracle 8 is new. Perhaps it will yeild better performance.

-Clark

Vikas Agnihotri wrote in message ...
>
>Need help from all you OLAP/dimensional modelling gurus out there.
>
>Oracle 7.3.4
>Typical star schema setup.
>

 ...
>All indexes involved are regular B*Tree indexes.
>
>Query:
> select
> Desc,sum(Attribute1),sum(Attribute2),sum(Attribute3),sum(Attribute4)
> from
> Dim1,Dim2,Dim3,Dim4,Fact
> where
> Dim1.key=Fact.dim1key
> and Dim2.key=Fact.dim2key
> and Dim3.key=Fact.dim3key
> and Dim4.key=Fact.dim4key
> and Dim3.Level = 'A'
> and Dim3.Date between Date1 and Date2
> and Dim1.Level = 'A'
> and Dim4.Level = 'A'
> and Dim2.Level = 'A'
>group by Desc
>
Received on Tue Jan 20 1998 - 00:00:00 CST