Re: index not used question

deposit_date is obviously NOT NULL and for the first sql the optimizer is able to get the count from the index alone, without having to access the table.

For the second sql Oracle must access the table and the optimized decided that it would be "cheaper" to just full scan the table. If that is the right choice depends on a lot of things.

Originally posted by Ted Chyn

> I have an index to deposit_date column which get used in

> #1 select count(*) where deposit_date =

>

> However, this index is not used when

> #2 select count(*) where deposit_date = and check_num like '%1642'

> the index column is on deposit only.

>

> Can any one shed light on this ? whyn adding check_num in where clause

> negate index usage(I am using oracle 9.2.0.3)

>

> thanks ted chyn(tedchyn_at_yahoo.com)

>

>

> ==

>

> #1

> 1 SELECT count(*) FROM PYMTITEM PI where (PI.DEPOSIT_DATE =

> 2 to_date('5-18-2003', 'MM-DD-YYYY'))

> 3* -- and (PI.CHECK_NUM like '%1642')

> SQL> /

>

> COUNT(*)

> ----------

>

>

>

> Execution Plan

> ----------------------------------------------------------

> SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=1 Bytes=7)

> 1 0 SORT (AGGREGATE)

> 2 1 INDEX (RANGE SCAN) OF 'PI_IS_1_FK' (NON-UNIQUE) (Cost=53

> Card=3774 Bytes=26418)

>

> #2

>

> 1 SELECT count(*) FROM PYMTITEM PI where (PI.DEPOSIT_DATE =

> 2 to_date('5-18-2003', 'MM-DD-YYYY'))

> 3* and (PI.CHECK_NUM like '%1642')

> SQL> /

>

> COUNT(*)

> ----------

>

>

>

> Execution Plan

> ----------------------------------------------------------

> SELECT STATEMENT Optimizer=CHOOSE (Cost=1022 Card=1 Bytes=22

> )

>

> 1 0 SORT (AGGREGATE)

> 2 1 TABLE ACCESS (FULL) OF 'PYMTITEM' (Cost=1022 Card=189 By

          tes=4158)

--
Posted via http://dbforums.com