index is not used when exists clause is used

From: Pan Yang Bin <panyb_at_hotmail.com>
Date: 17 Nov 2003 01:34:42 -0800
Message-ID: <f258af3d.0311170134.6ed1dde7@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. Received on Mon Nov 17 2003 - 03:34:42 CST