Professional Web Applications Themes

index is not used when exists clause is used - Oracle Server

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 ...

  1. #1

    Default index is not used when exists clause is used

    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.
    Pan Guest

  2. #2

    Default Re: index is not used when exists clause is used


    Uzytkownik "Pan Yang Bin" <com> napisal w wiadomosci
    news:google.com... 

    exists is fired for each row, no matter there are any indexes.

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

    --
    Noel




    Noel Guest

  3. #3

    Default Re: index is not used when exists clause is used

    com (Pan Yang Bin) wrote in message news:<google.com>... 


    There isn't any predicate for t1 in the where clause of the update.
    The predicate in the subquery is irrelevant in this case, as the
    subquery is a different query block and hence t1.f1 is
    derived/correlated from the main query. To better understand what
    happens the predicate should have read
    t2.t1f1 = t1.f1
    So nothing unusual and nothing unexpected, Oracle is just doing what
    you are asking it: you don't have any predicates on t1, so Oracle
    won't use the idex.

    Sybrand Bakker
    Senior Oracle DBA
    sybrandb@yahoo.com Guest

  4. #4

    Default Re: index is not used when exists clause is used

    com (Pan Yang Bin) wrote in message news:<google.com>... 

    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.
    Tony Guest

  5. #5

    Default Re: index is not used when exists clause is used

    com (Pan Yang Bin) wrote in message news:<google.com>... 

    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
    Steve Guest

Similar Threads

  1. Replies: 4
    Last Post: August 3rd, 03:11 PM
  2. method name exists, property value exists, calling method fails
    By Phil Powell in forum PHP Development
    Replies: 0
    Last Post: October 27th, 07:34 PM
  3. Replies: 3
    Last Post: October 1st, 07:40 PM
  4. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139