Re: Oracle ignoring function-based index - why?

On 25 Jul 2005 09:34:48 -0700, "Anurag Varma" <avoracle_at_gmail.com> wrote:

>Sybrand,
>
>Can you elaborate where oracle recommends a 20 percent value for
>estimate_statistics?
>I've seen oracle recommend using auto sample size. However, nowhere
>have I seen a number like 20% recommended.
>Also, the default for histogram is 1.
>http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003993
>
>"FOR ALL COLUMNS SIZE 1" does not gather any histogram. Its not
>ridiculous as you claim it to be. If OP does not want to gather any
>histograms than this value if fine.
>
>Also, your suggestion of a default 200 value for all indexed columns is
>ridiculous (I'm using your lingo now). Oracle suggests using "AUTO".
>Gathering histograms for
>columns which are not skewed will only harm performance. Also,
>non-indexed columns
>can benefit from histograms.
>
>Also, degree of parallelization is not necessarily dependent on number
>of CPU's. This has been debated a number of times in this forum. Try
>googling this.
>
>Anurag

As you may well know by now (as I have posted it many times) most of our customers still run 8i, they have no intention to upgrade, and we are not pressing them. In fact, it seems we are going to maintain a 'new' 7.3.4 database, which can't be upgraded because the client app has phased out.
So you shouldn't criticize me for using 8i terminology, as you may be aware 'auto' sized histograms simply don't exist in 8i. The value of 200 is not ridiculous, as Oracle won't create 200 buckets if it doesn't need to.

--
Sybrand Bakker, Senior Oracle DBA