Re: Star Schema Performance Problems?

Star does not work well, in general,
on Oracle 8.0.

The mechanism is for Oracle to go
round all the viable dimension tables
collecting primary keys, then build
a Cartesian product that it uses to
probe the best concatenated index
on the fact table.

This can work reasonably well if

a)    the dimensions are small
b)    the fact table is very dense
c)    you can create all the best possible indexes.

However, even on very small dimensions
(say 100 hit primary keys on each of 3)
a Cartesian join gives a lot of targets (in the example 1,000,000 combinations to try).

You have a dozen small dimensions -
how many really good combination indexes have you been able to build.

I can promise you that star-transformations in 8.1 using bitmaps on the fact table are vastly more efficient in almost all cases.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

reash_at_attglobal.net wrote in message <3957C2ED.7E7F6EA5_at_attglobal.net>...


>8.0 Oracle and b-tree indexes.

>

>Randy

>

>Jonathan Lewis wrote:

>

>> Which version of Oracle ?

>> Are you talking about 8.1 and

>> bitmapped star transformation,

>> or 8.0 and concatenated b-tree

>> indexing ?

>>