RE: Why isn't Oracle Using My Index

Olfgang,

Thanks for the response. It was pointed out to me that my initial query and results were incorrect and I did resend the corrected query and results. Here too is the informaiton about the data in this particular column.

SQL> select count (*) from aradmin.t185;

  COUNT(*)

     41586

SQL> select count(unique(c1)) from aradmin.t185;

COUNT(UNIQUE(C1))

            41586

SQL> select c1,count(*) from aradmin.t185   2 group by c1 having count(c1) > 1;

no rows selected

Thank you.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Thursday, December 21, 2006 10:58 AM To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: Re: Why isn't Oracle Using My Index

At 10:49 AM 12/21/2006, William Wagman wrote:
>Greetings,
>
>This is a question I have been looking at and puzzling over for a
couple
>of days and am unable to explain, I'm hoping someone can help me
>understand what is going on. In a 9i database I have a table with 41550
>rows on which stistics are generated weekly. In looking at a simple
>select the query does not use an index and I am unable to figure out
how
>to make it use the index.
>
>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.

You say c1 is unique. Oracle and the index stats don't seem to know that. How can you have avg_leaf_blocks_per_key > 1 for an index on a unique column?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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