Re: index is not used when exists clause is used

panyb_at_hotmail.com (Pan Yang Bin) wrote in message news:<f258af3d.0311170134.6ed1dde7_at_posting.google.com>...
> Hi All,
> Here is the situation I met:
>
> CREATE TABLE t1(f1 NUMBER, f2 VARCHAR2(20));
> CREATE UNIQUE INDEX i1 ON t1 ( f1 );
> CREATE TABLE t2(f1 NUMBER,f2 VARCHAR2(10), t1f1 NUMBER);
> CREATE INDEX i2 ON t2(f1);
>
> after explain following SQL
> update t1
> set f2 = 'abc'
> where exists(select 1 from t2 where t1.f1 = t2.t1f1 and t2.f1 = 1);
> The result:
> 0 UPDATE STATEMENT Optimizer=CHOOSE
> 1 0 UPDATE OF 'T1'
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T1'
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
> 5 4 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)
> T1 was full table scaned and the index I1 was not used.
>
> And I try to explain following SQL
> select t1.* from t1, t2 where t1.f1 = t2.t1f1 and t2.f1 = 1;
> The result:
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 NESTED LOOPS
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
> 3 2 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)
> 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
> 5 4 INDEX (UNIQUE SCAN) OF 'I1' (UNIQUE)
>
> And I1 was used.
>
> So can anybody tell me why index I1 was not used in the SQL--UPDATE
> when I use EXISTS? I also try to add hint /*+ index(t1 f1) */ after
> UPDATE or SELECT(before 1), the index was still not used. How can I
> use index I1 in my UPDATE?
>
> thanx.

How many rows do you have? If you have good stats, then the optimizer calculated a lower cost for a full scan. Index does not always = good. :)

Regards,

Steve Received on Mon Nov 17 2003 - 11:16:01 CST