Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Star Query Indexes
> I am a little puzzled at how to implement indexes on a table which is the
> central table in a Data Mart with approximately 10 associated dimension
tables.
>
> I am using Oracle 7.3.2.1.0 and the documentation states that
>
> "The cost-based optimizer intelligently searches for potential star
queries
> based on two criteria.
> 1. One table is significantly larger than any other table in the query
> 2. The larger table contains a concatenated index"
>
As far as I know, there is a 7.3 feature in the Oracle (cost-based)
optimizer that "recognizes" the potential for a Star Join, based on table
sizes, when the JOIN in question involves one "big" table & several "small"
tables (so far, I have not seen anything that supplies details about how
big & small are determined...) The optimizer chooses an execution plan
which forms a Cartesian Product of the selected rows from the small
(dimension) tables, then uses the concatenated PKs of the dimension
cartesian product rows to index into the fact table (via a nested loop.)
However, for this to work the fact table must have a composite key on the
FK columns defined on the PKs of the dimensions, and the query must include
all the dimensions for the leading components of the composite key. The
"standard" star schemas that I have seen usually define the PK on the fact
table as a composite of the FK columns defined on the PKs of the dimension
tables -- in such a scheme, the new star join optimzation should work....
Received on Mon Nov 04 1996 - 00:00:00 CST