Re: Star Query Indexes

How many columns with high-cardinality are you looking at in a single query. Do most queries involve perhaps one or two high-cardinality dimensions (customer and/or product). Most of the other dimensions are low-cardinality (age group, income band, race, gender, etc.)? In fact, most dimensions are so small as to qualify as mini-dimensions of customer or product. Also, rather than hard-coding the fact that a table is always going to be used as a fact table, you can use the 'STAR' optimizer hint in the query itself.

"Larry Monack" <lmonack_at_sprynet.com> wrote:

>Bitmapped indexes are only good for columns with low cardinality. I agree
>with Mike's point which is referring to how Oracle's optimizer recognizes a
>'star' query as opposed to a 'pair-wise' query. Surely, the minds at
>Oracle can come up with a better way like allowing the DBA to tag the fact
>table as a 'fact' table. In the meantime, we're stuck with the pair-wise
>query. And to say Oracle has 'Star Query' functionality is just pure
>marketing hype.
 

>>>Oracle's Star Query functionality is useless
>>>unless your fact table table has only a few keys, and you use ALL the keys in a
>>>query.
>>>If you have more than say, five key fields, it is unreasonable to create a
>>>concatenated index on all of them and also to expect users to always create
>>>query's based on values for all of those key fields.

• Robert Gordon