Re: index not used question

You can FORCE Oracle to use the index and see if it is faster ...

SELECT * /*+ INDEX(table_name index_name) */ FROM table_name ...

Domenic.

tedchyn_at_yahoo.com (Ted Chyn) 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 - 14:52:03 CDT