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.

Because EXISTS tends to be processed like this pseudo-code:

FOR each row in t1 LOOP
  IF matching t2 row exists THEN
    UPDATE t1
  END IF
END LOOP If you really need to use that index, it would be better to rewrite as:

update t1
set f2 = 'abc'
where t1.f1 in (select t2.t1f1 from t2 where t2.f1 = 1);

On the other hand, if more than a small proportion of the rows will be updated, the full table scan may be the best approach anyway. Received on Mon Nov 17 2003 - 10:10:00 CST