Why isn't Oracle Using My Index
- From: "William Wagman" <wjwagman@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 09:49:50 -0800
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@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
Other related posts:
