Mark D Powell wrote:
> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<MyFNb.82210$Vs3.69341_at_twister.socal.rr.com>...
>
>>I'm looking for suggestions and experience on dealing with skewed data
>>in a datawarehouse. Unfortunately, I don't see how histograms can help
>>with what I believe is a fairly standard star schema. Imagine a
>>dimension table D that has two rows with natural keys 'D1' and 'D2'.
>>Imagine a fact table F that has 10 million rows. The typical query
>>looks something like this:
>>
>>select f.*
>>from d, f
>>where d.key = 'D1'
>> and f.d_id = d.d_id
>>
>>Now let's say 99% of those rows belong to dimension 'D1' and 1% to 'D2'.
>> When querying with 'D1' we want to do a full table scan, with 'D2' an
>>index scan is faster.
>>
>>What can be done to get the optimizer on track? Obviously, we could
>>eliminate the surrogate keys and then build a histogram on the fact
>>table. Is that how this is typically handled? We're also considering
>>bitmap join indexes but in reality these queries typically select many
>>columns from the dimension tables as well.
>>
>>Ideas?
>>
>>
>>Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
>>With the Partitioning, OLAP and Oracle Data Mining options
>>JServer Release 9.2.0.2.0 - Production
>
>
> Richard, we had a situation similar to your in our OLTP. The CBO said
> full table scan. The developer wanted the query to use an index. The
> index only had six distinct values of which 85% of the data had one
> value.
>
> I looked at it and demonstrated that the CBO was correct for 85% of
> the data in our case so the developer coded a test on the bind
> variable. If it was the 85% value we let the optimizer run the
> statement the way it wants. For any other value we execute a hinted
> version of the statement that uses the index.
>
> HTH -- Mark D Powell --
If there is any chance you could send me the item you just described I
would very much like to show it to my students. Thanks.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Fri Jan 16 2004 - 10:05:11 CST
