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 CET