Re: Star Query Indexes

mike_at_mrush.demon.co.uk (Mike Rushton) wrote:

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

>Is the second of the criteria suggesting a single index on all ten dimension
>key fields? If so what if you query the table using only five of the keys, the
>index may be useless.
 

>What is the normal method for indexing a Data Mart fact table? Separate indexes
>for each dimension key?

I believe this is essentailly how the optimizer recognizes a star query - except that I think there not only has to be an index with a concatenated key of the other tables involved in the join, but it must be a Primary Key Index. Whether or not the optimizer actually uses this index is a secondary issue. By seeing a concatenated PK in a larger table, it forces the cartesian join between the smaller tables and defers the "big" join for last.

You may decide to use multiple b-tree indexes on a fact table, or you may decide to use bitmap indexes for low-cardinality dimension keys. However, the optimizer's recognition of a star query and its decision which (if any) indexes to use based on your constraints in the join SQL are still separate decisions.

• Robert Gordon