Re: Indexing data warehouses

In article <4ejug9$j2t_at_reader2.ix.netcom.com>, nraden_at_ix.netcom.com says...
>
>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.

Wouldn't you also index the individual columns in the key (given low cardinality of their values) and, thus, the second query would, at least, hit one of those indexes?

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

Isn't Oracle's parallel indexing approach intended to deal with this issue?

>
>--
>_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>_/ 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
>_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>
>
 

-- 
-------------------------------------------
              Benjamin Taub
             btaub_at_dspace.com
          DATASPACE INCORPORATED
            ph:  (313) 761-5962
            fax: (313) 761-5967
       DSS & Data Warehouse design,
development, project management, & training