Niall,
Thanks for the answer. Yes, certain...
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
SQL>
That is why this is so puzzling to me.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
------------------------------
*From:* Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
*Sent:* Thursday, December 21, 2006 10:22 AM
*To:* William Wagman
*Subject:* Re: Why isn't Oracle Using My Index
Are you absolutely sure that the index is unique? the cbo seems to be
expecting 413 rows back or 10% or so of the table.
On 12/21/06, William Wagman < wjwagman@xxxxxxxxxxx> 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.
>
> 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
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
