Re: Why isn't Oracle Using My Index

Ah

I didn't mean was the data unique, but was the index created as unique (or a unique constraint upon the column). For indexes created as

create unique index ... then the optimizer will know for sure that you will only get one row back here. For non-unique indexes CREATE INDEX .. it won't and will make selectivity estimates based on the stats.

I'd also be interested in what your gather stats command is and what the column stats are currently.

On 12/21/06, William Wagman <wjwagman_at_ucdavis.edu> wrote:
>
> 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_at_ucdavis.edu
> (530) 754-6208
>
>
> ------------------------------
> *From:* Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
> *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_at_ucdavis.edu> 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_at_ucdavis.edu
> > (530) 754-6208
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l