Re: Data Warehouses & Indexes on 9i

An analysis of all your existing objects absolutely could not hurt, providing of course you do not have massive tables and indices and you run this in production in your peak duty cycle.

From what you have already mentioned, I suspect you know about when to apply BMI's (the classic case being a column called "GENDER"; on the other hand, these days...;)

Well that analysis will help by giving you cardinality analysis based on the actual content of your tables, not to mention giving the CBO fresh things to run from.

Generally by the nature of columns that are players in composite primary keys, or in major composite indexes (er, indices), by looking at the data you are stuffing in there, you can usually, but not ALWAYS, judge what to do.

Every time I am about to lecture on something like this, I remember my friend Walt, who did a complete stochastic and analytical tabulation of the United States Social Security Numbers for, well, I guess about a quarter million people or so.

Once I got past the: "WHY did you do this?" through the "WHY would ANYBODY do this?", to the "Okay, if it was anybody but you.." stage, it was revealed to me that SSN's at least in our population, were not evenly distributed, as "one would think".

It wasn't even a (pardon the pun) bell curve, it looked more like the ECG trace on a patient with a bizarre heart problem.

Why was this important?

We had to create hashing methods to put people's data in the right datasets; if it were not for his very well reasoned analysis and the discovery of such an anisotropic distribution of SSN's among 250,000 employees, our systems would have been garbage.

The point being, never assume anything about something that is a root ingredient in your system. Which, in turn, also means, there is no such thing as a stupid question.

I will be back, KFC closes soon. :)

RSH.
"Carole Kaufman" <carolek_at_ix.netcom.com> wrote in message news:72330e0d.0205021812.680725d6_at_posting.google.com...
> We're building a traditional star schema data warehouse which loads
> every hour even during active query use.
>
> What indexing strategy do you recommend for the dimension tables
> (index b-tree / bitmap all fields depending on cardinality?) some
> fields? only FK fields?
>
> What indexing strategy do you recommend for the fact table? Bitmap
> joiners with all FK keys?
>
> What indexing strategy do you recommend we stay away from? Do I have
> to be careful with indexes if I'm loading and querying at the same
> time?
>
> Is there an average % of index to data for data warehouses? Or is it
> all over the board?
>
> Thanks,
>
> Carole
Received on Thu May 02 2002 - 22:42:09 CDT