RE: Why isn't Oracle Using My Index
- From: "William Wagman" <wjwagman@xxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 11:03:04 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: