Re: searching for records with skewed data values (skewed data changes rapidly)

Daniel,

I agree with you - the partitioning option is too expensive and this is a regularly updated table. I would guess that it is not suitable for a bitmap - but I would have to test to be sure. In any case, I think that this is a simpler issue relating to histogram gathering.

>>The question I have for Matt is simple ... why do you care? What is the
>>issue? And how do you know the issue exists?

I care because I can see that the server is doing lots of full scans (I see the reads in performance monitor). A statspack report highlights that this statement carries out 30-50% of the I/O (depending on time of day). When the query does an FTS it takes 8-10 seconds, with an index hint it takes 0.1 seconds. So, I know that the index is the way to go - I just can't seem to get the optimizer to choose the index.

I think that the issue is that my dbms_stats call is incorrect and not generating sufficient buckets "for columns status size x" does not seem to create x buckets. Either there is something wrong with the call or I misunderstand how histograms work.

I'm away from a test machine for a few days, so can'y say much more at the moment.

Hey, if you can reference any good detailed material on histograms - I will read it (I read the tahiti doco, need something with more real world examples).

I appreciate the responses.

Mat.

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1092752945.545275_at_yasure>...
> Pratap wrote:
>
> > You can try partitioning the table on that column. So partition full
> > scans hurt that much. You may also test with bitmap indexes.
> >
> > Pratap Deshmukh
> > Cognizant Technology Solutions, India
>
> If updates are taking place this might not be a good place for a bitmap
> index. And if the values are there only for short periods of time I'd
> argue that partitioning would be very expensive both in dollars and in
> the time required for records to move from partition to partition.
>
> The question I have for Matt is simple ... why do you care? What is the
> issue? And how do you know the issue exists?
Received on Thu Aug 19 2004 - 04:53:24 CDT