Unless you have a function based index, a LIKE will not use an index.
"Ted Chyn" <tedchyn_at_yahoo.com> wrote in message
news:44a19320.0309110707.1278bc69_at_posting.google.com...
> 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(*)
> ----------
> 0
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 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(*)
> ----------
> 0
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 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)
Received on Fri Sep 12 2003 - 13:49:48 CDT
