Re: index not used question

Hi Ted,

Very Simple :

In the first query, the optimizer realizes it can find the rows by using the index only.

But in the second query, if it first used the index to find the rows satisfying the first predicate, it would still have to scan through the row data to find those satisfying the second one, and that's one index scan + one full table scan, which costs more than one full table scan => optimizer correctly chooses full table scan.

• Kenneth Koenraadt

On 11 Sep 2003 08:07:22 -0700, tedchyn_at_yahoo.com (Ted Chyn) wrote:

>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 Thu Sep 11 2003 - 14:25:36 CDT