| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index
Tom,
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.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]
Sent: Thursday, December 21, 2006 10:40 AM
To: William Wagman; oracle-l_at_freelists.org
Subject: RE: Why isn't Oracle Using My Index
Bill,
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!
Tom
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman
Sent: Thursday, December 21, 2006 12:50 PM
To: oracle-l_at_freelists.org
Subject: Why isn't Oracle Using My Index
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.
TABLE_NAME BLEVEL AVG_DATA_BLOCKS_PER_KEY ------------------------------ ---------- -----------------------AVG_LEAF_BLOCKS_PER_KEY AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
----------------------- ----------------------- -----------------
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.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 21 2006 - 13:04:37 CST
 |
 |