Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: searching for records with skewed data values (skewed data changes rapidly)
mathewbutler_at_yahoo.com (Matt) wrote:
> 9.2.0.4.0 Win 2K SP5
>
> What options are there to take when a column may take one a 5 values
> to indicate a records state (states are skewed).
Is the nature of the skew permanent (a few "A" and many "B", all the time), or does the skew oscillate (beginning of the month has many "A", few "B", middel of month has few "A", many "B")?
> The state gets
> updated so that some states exist for short periods of time, so short
> that they are sometimes not caught in statistics
This doesn't make sense to me, unless the skewness oscillates wildly. The nature of statistics is, well, statistical. It doesn't matter whether any particular record is in some particular state at the time statistics are gathered.
> causing expensive FTS
> rather than index accesses.
This seems even more unlikely. If a status is so rare that it gets missed altogether, this should cause the CBO to error (if at all) in the direction of index-based lookups, rather than in the direction of FTS. No?
> 1) I can hint the query
> 2) I can use plan stability
> 3) Anything else....?
-1) Verify that you are in fact collecting histogram data, rather than just table statistics without histograms.
0) Not use bind variables for the status part of the where clause.
Xho
-- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GBReceived on Wed Aug 18 2004 - 12:26:21 CDT