Re: Indexing data warehouses

In <4ejab5$77f_at_suba01.suba.com> gnilges_at_qni.com (Gary Nilges) writes:
>
>I was told that data warehouses traditionally didn't have alot of
>indexes. I would think, though, that since this is a read-only OLAP
>instance with users pounding the heck out of it with a querey tool,
>that you'd want to index the heck out of it.
>
>What are the drawbacks (if any) to indexing in a read-only data
>warehouse? Was I told wrong, or are DW's usually not highly indexed?
>This is an Oracle 7 installation on an HP UNIX box, btw.

If you have a sensible query optimizer, a lot of indexing isn't necessary. For example, in a simple star schema, we may have a five-segment key (one segment for each "dimension") and a single compound index. What happens if you query based on keys 1,2,3? Probably an index scan. What about 2,3,4? In Oracle, a table scan. So if Oracle is your database, you'll need that 2,3,4 combination index too. Note that Red Brick, a database optimized for this sort of thing, does not require all of these indices.

Now, what's the drawback? On any given query, provided the query plan uses the right index, no problem. But data warehouses frequently have trouble getting updated within a small window of time, and all of the extra indices extend the time it takes to load. That and the extra space are the biggest drawbacks.

-- 
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Neil Raden - nraden_at_ix.netcom.com or NRaden_at_aol.com
_/ 
_/ Archer Decision Sciences, Inc.
_/ 133 E. De La Guerra Street
_/ Santa Barbara, CA 93101
_/
_/Decision Support, Data Warehousing Consulting
_/ CA:  805.963.0017      NY:  914.987.9528    
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/