Re: FW: Why isn't Oracle Using My Index

At 11:58 AM 12/21/2006, William Wagman wrote:
>I made an error initially when I sent the information re the clustering
>factor, here is the correct query...
>TABLE_NAME BLEVEL AVG_DATA_BLOCKS_PER_KEY
>------------------------------ ---------- -----------------------
>AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
>----------------------- -----------------
>T185 1 1
> 1 10276
>
>SQL> set autotrace traceonly explain;
>SQL> SELECT C240000008 FROM aradmin.t185 WHERE C1 = 'HD0000000041608'
> 2 /
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1420 Card=413 Bytes=
> 627760)
>
> 1 0 TABLE ACCESS (FULL) OF 'T185' (Cost=1420 Card=413 Bytes=62
> 7760)
>
>There is an index IT185 on column C1 and column C1 is unique. A hint
>will force the use of the index but in that this is not a locally
>developed application I am unable to change the code. Nevertheless, in
>attempting to understand this I looked at the clustering factor for the
>index.

Is the index declared as unique? Why would Oracle assume that 413 rows qualify for an equality predicate on a unique column?? what do the column statistics (num_distinct, density, buckets) say for c1.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l