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@xxxxxxxxxxx
(530) 754-6208
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
Sent: Thursday, December 21, 2006 10:58 AM
To: William Wagman
Cc: oracle-l@xxxxxxxxxxxxx
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

Other related posts: