| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index selectivity
Franz Mueller wrote:
>
> Hi,
>
> does anybody know how exactly the 'compute statistics' works?
> If you have a table with an index on col1 and the table has 1000
> entries and 1000 different entries in col1, you have a selectivity of
> 1/1000 i.e. the index will be used. If you have 500 entries with
> value1 and 500 entries with value2, the selectivity is 1/2 and the
> index will not be used. What matters if you have 999 entries with
> value1 and 1 value with value2? Does the selectivity remain 1/2 and
> the index will NEVER be used or is the optimizer so clever to know
> that the selectivity of value1 is 1 (no use of index) and the
> selectivity of value2 is 1/1000 (use index)?
>
> Thanks
> Franz
The optimiser assumes an even distribution. But you can create histograms on columns during analyze (see the doco for ANALYZE) which then stores the distribution of the data. The optimiser can then use this to make more informed decisions on whether to use the index or not.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Oct 19 1999 - 06:51:31 CDT
 |
 |