Re: index selection!

The 'estimate statistics for column' version of the command generates a histogram of the value distribution across the column, so that Oracle will be able to determine that there are very few F and almost all T, consequently allowing a query of

    tf_col = 'F' to use the index whilst     tf_col = 'T' uses a tablescan

The simple 'analyze table' command will only generate 'average stats' for each column viz:

    number of times a column is not null     number of distinct values
    high value / low value.

So in this case, Oracle will only be able to calculate:

    total number of filled columns = 70,000 (if I recall correctly)     distinct values = 2
    low value = F
    high value = t

Inference - there are 35,000 rows at F, and 35,000 at T so a tablescan is inevitably the best option.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

EnderW wrote in message <8pf00p$923$1_at_nnrp1.deja.com>...


>I cannot understand how this would help since if you do analyze table,

>it should create statistics for columns as well. Estimating might help

>since it should push the optimizer to an index based plan rather than

>full table scan. However I am kind of not sure whether it would create

>a full table scan path when you want it to.

>

>analyze table mytable estimate statistics sample 10 percent ;

>

>

>In article <968522928.12699.0.nnrp-08.9e984b29_at_news.demon.co.uk>,

>  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>> The command you need is a variant of :

>>     analyze table xyz estimate statistics for columns tf_col;

>>

>> --

>>

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> wy_at_fudan.edu wrote in message <8pc86s$c9c$1_at_nnrp1.deja.com>...

>> >I have run 'analyze table incard compute statistics'

>> >       and 'analyze index idx_bit_incard_if_chk compute statistics'

>> >but it does not work!

>> >

>>

>>

>

>--

>Ender Wiggin

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.