Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index

RE: Why isn't Oracle Using My Index

From: <>
Date: Thu, 21 Dec 2006 11:04:37 -0800
Message-ID: <>


That is a good question. I am responding to users saying that the particular piece of the application in which this code is executed is too slow. So I am basically responding to users requests to speed things up.


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208

-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[] Sent: Thursday, December 21, 2006 10:40 AM To: William Wagman; Subject: RE: Why isn't Oracle Using My Index


Instead of answering your question, let me ask another one?

Why do you think it will be faster to use an index rather than the way that Oracle has decided to do it? The cost is relatively low. What is the response time for the query?

Sometimes, Oracle *does* know best!


This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----Original Message-----

[] On Behalf Of William Wagman Sent: Thursday, December 21, 2006 12:50 PM To:
Subject: Why isn't Oracle Using My Index


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=

   1    0   TABLE ACCESS (FULL) OF 'T185' (Cost=1420 Card=413 Bytes=62

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.

------------------------------ ---------- -----------------------
----------------------- ----------------------- -----------------
T185                                    1                       1
                      1                   10276             10276

I see that the clustering_factor is quite high (in fact equal to avg_leaf_blocks_per_key) which as I understand it is why the optimizer is not using the index. Following the discussion of db_file_multiblock_read_count which has been taking place recently I played with changing it. It is currently set to 8 and I reduced it to 1 but that made no difference. There are a large nukber of blocks in the table

TABLE_NAME                         BLOCKS
------------------------------ ----------
T185                                 9345

Which as I understand it also goes into the optimizer's decision to do a full table scan.

So, my question, how can I get this thing to use the index without changing the code? Can I? I don't know if it worth looking at OPTIMIZER_INDEX_CACHING or OPTIMIZER_INDEX_COST_ADJ or if there is some other method whereby I can get the optimizer to do an index scan instead of the table scan. Any suggestions greatly appreciated.


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208

Received on Thu Dec 21 2006 - 13:04:37 CST

Original text of this message