FW: Why isn't Oracle Using My Index

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Thu, 21 Dec 2006 10:58:29 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2AB7ABEF@VEXBE2.ex.ad3.ucdavis.edu>

Hello,

I made an error initially when I sent the information re the clustering factor, here is the correct query...

SQL> select table_name,blevel,avg_data_blocks_per_key,   2 avg_leaf_blocks_per_key,clustering_factor   3 from user_indexes
  4 where table_name = 'T185' and index_name = 'IT185';

TABLE_NAME                         BLEVEL AVG_DATA_BLOCKS_PER_KEY


------------------------------ ---------- -----------------------

AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
----------------------- -----------------

T185                                    1                       1
                      1             10276

Thanks.

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-l Received on Thu Dec 21 2006 - 12:58:29 CST