SQL conditions - use of NOT
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQL conditions - use of NOT

  1. #1
    Join Date
    Nov 2000
    Posts
    15

    SQL conditions - use of NOT

    I administer a multi terabyte ODS on 9i release 2. The database recieves a lot of AD HOC sql, and as a result I get numerous inquiries as to what a given process is doing. Often these long running queries have high costs associated with them. Somtimes it is obvious what drives the up the cost of the query, somtimes not so. Often I see high cost queries associated with SQL statements using NOT logic. When I see this condition, I suggest that the user attempt to eliminate the NOT logic and find some other way to perform their SQL. I have heard in the past that NOT, NOT EXISTS, NOT IN, != should be avoided at all costs as their use will always produce high cost, slow running queries. Is there any validity to this claim? and if so, why?
    Michael Auer
    Oracle DBA/Developer
    Oracle8i OCP

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The designer should have thought the performance of the system even before creating a table. Now it is too late. Most of D/W systems are built in this fashion.
    However, if you understand the application and the business logic, you can rewrite the code. A tough task ahead.

    Good Luck
    Tamil

  3. #3
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16
    The reason is that simply any of those statements disallow index usage. An index is built to tell Oracle what exists in a table, but it can not tell it what does not exist. Therefore, queries on a table with negative logic can typically force full table scans.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by DreamWarrior
    The reason is that simply any of those statements disallow index usage.
    Where do you get that from?
    Code:
    After creating a PK on dept(deptno)
    
    select * from emp 
    where deptno not in (select deptno from dept)
    /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=32)
       3    1     INDEX (FULL SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=1 Card=1 Bytes=2)
    
    
    select * from emp e
    where not exists
    (select * from dept d
     where  d.deptno=e.deptno)
    /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=32)
       3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
    I would agree that NOT logic makes it less likely that an index will be used, simply because it is typically less selective and if, say, 95% of a table will be selected, a FTS is preferable. Trivial example:
    SELECT * FROM my_table WHERE primaryKex <> 123;
    will select the whole table less (possibly) one row and hence use FTS.
    (No prizes given for constructing counter examples where <> is more selective than =).

    I would add once again my preference for (NOT) EXISTS over (NOT) IN
    - NOT IN handles NULL's in an unexpected way.
    - in my experience (NOT) EXISTS performs as well or better than (NOT) IN and almost never worse (check the different index use in the explain plans above) - though the Oracle Tuning Manual gives two examples where IN is claimed to be better than EXISTS.

  5. #5
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16
    Originally posted by DaPi
    Where do you get that from?
    Code:
    After creating a PK on dept(deptno)
    
    select * from emp 
    where deptno not in (select deptno from dept)
    /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=32)
       3    1     INDEX (FULL SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=1 Card=1 Bytes=2)
    
    
    select * from emp e
    where not exists
    (select * from dept d
     where  d.deptno=e.deptno)
    /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=32)
       3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
    *snip*
    Grand, you just proved my point. Thanks. Did you not see that EMP used a full table scan? The indexed scan of DEPT_PK was done on the dept table as a result of the SELECT * from DEPT. Any index in that column in the EMP table would not be used.

    An index is built based on what is IN a table, PERIOD! Indexes for everything NOT in the table do not, and can not, exist.

    If you have an index on col1 of table1 the following query could NOT use the index on col1:

    SELECT * FROM table1 WHERE col1 != value;

    Neither could:

    SELECT * FROM table1 WHERE col1 NOT IN (val1, val2, etc..);

    Nor could any other statement that asks for information NOT IN the table. Because that information is also NOT IN the index.

    BTW: I got the information from a O'Reilly's Oracle Performance Tuning book. And it makes complete sense if you think about it.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by DreamWarrior
    If you have an index on col1 of table1 the following query could NOT use the index on col1:

    SELECT * FROM table1 WHERE col1 != value;
    Code:
     select count(*) from emp where empno <> 23;
    
    xecution Plan
    ---------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
      1    0   SORT (AGGREGATE)
      2    1     INDEX (FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=13 B ytes=39)
    OK, SELECT * DOES use a FTS, but that's sensible because you need to read the table to get all the columns. You can bully it into using an index by using a hint (these tables are really too small for our purposes - but it's all I have to hand at the moment).

    I still think "any of those statements disallow index usage" is too strong. I would just say it makes it less likely to use indexes.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DreamWarrior
    An index is built based on what is IN a table, PERIOD! Indexes for everything NOT in the table do not, and can not, exist.
    Ofcourse, but is it relevent here to the post?

    Querying data using NOT will have to fetch records that does not match equality contition, Ok does that mean Index for NOT can not exist..i really dont understand what do u mean here.


    BTW, with queries using NOT will not use INDEX only because it has to do INDEX FULL scan and the table access by ROWID which will be much costlier in terms of I/O ( Full Scan will fetch only 1 block per i/o where as FTS can DMBC blocks per I/O )

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16
    Originally posted by DaPi
    Code:
     select count(*) from emp where empno <> 23;
    
    xecution Plan
    ---------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
      1    0   SORT (AGGREGATE)
      2    1     INDEX (FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=13 B ytes=39)
    OK, SELECT * DOES use a FTS, but that's sensible because you need to read the table to get all the columns. You can bully it into using an index by using a hint (these tables are really too small for our purposes - but it's all I have to hand at the moment).

    I still think "any of those statements disallow index usage" is too strong. I would just say it makes it less likely to use indexes.
    Just because you are selecting all the columns does not disallow the usage of an index or even have much to do with whether or not the optimizer chooses to use one. The optimizer selects index usage based on the where clause, the selected columns has little to do with it (unless of course it can obtain all selected values from an index - or series thereof).

    Read the O'rielly Oracle Performance Tuning book, here I'll quote it for you:

    pg. 172:
    "In the following example, the "!=" (not equal) function cannot use an index. Remember that indexes can tell you what is in a table but not what is not in a table. All references to NOT, "!=", and "<>" disable index usage."
    This is taken from the 2nd edition of O'Reilly Oracle Performance Tuning. While it may be a bit dated, unless Oracle found a magic genie to put in their indexes and know the limitless possibilities of things not existing in the table to go out to the table and get them, I don't think that basic information has changed.

  9. #9
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16
    Originally posted by abhaysk
    Ofcourse, but is it relevent here to the post?

    Querying data using NOT will have to fetch records that does not match equality contition, Ok does that mean Index for NOT can not exist..i really dont understand what do u mean here.


    BTW, with queries using NOT will not use INDEX only because it has to do INDEX FULL scan and the table access by ROWID which will be much costlier in terms of I/O ( Full Scan will fetch only 1 block per i/o where as FTS can DMBC blocks per I/O )

    Abhay.
    It is entirely relevent, read my above post. What would you like Oracle to do, scan the entire Index and remember everything IN the table where your != statement is false, then go back to read the entire table, but only dispatch rows that were not remembered from the index scan? Sounds like double duty to me.

    If you ask Oracle to:

    Select from table where column != value.

    or

    Select from table where column NOT IN (value1, value2, etc.)

    Then an index on column can NOT BE USED!

    Now possibly if you had an index on column and column2, and appended a where qualifier dealing with column2 that could use an index, then it MAY be able to use that index, but only because of column2.

    Anyway, I'm done trying to explain this...it makes sense to me, you keep on using != and NOT IN when you could use something different (>, <, maybe an IN statement when you have a discrete number of possibilities in the value set).

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DreamWarrior
    It is entirely relevent, read my above post. What would you like Oracle to do, scan the entire Index and remember everything IN the table where your != statement is false, then go back to read the entire table, but only dispatch rows that were not remembered from the index scan? Sounds like double duty to me.
    I dont know a bit what are you talking, do you know how will index search work?

    Well its not that "scan the entire Index and remember everything IN the table where your != statement is false, then go back to read the entire table, but only dispatch rows that were not remembered from the index scan? Sounds like double duty to me"

    But,Its some thing like this "It will return those rows(ROWIDS) which dont match the value spicfied." Does this statement sound correct?

    Originally posted by DreamWarrior
    If you ask Oracle to:

    Select from table where column != value.

    or

    Select from table where column NOT IN (value1, value2, etc.)

    Then an index on column can NOT BE USED!
    Just see the behaviour of the index usage, you can see the response time is high for FULL SCAN of Index
    and then return ROWIDS that dont match & then eventually the data being picked up from the table.

    Rather it would be easy for Oracle to do FTS and return non matched values.

    Code:
    TEST_ABHAY> set autotrace on explain
    TEST_ABHAY> select * from test where ID4!=1;
    
                      ID                  ID1                  ID2                  ID4
    -------------------- -------------------- -------------------- --------------------
                 1999989              1999990              1999991                    2
                 1999990              1999991              1999992                    2
                 1999991              1999992              1999993                    2
                 1999992              1999993              1999994                    2
                 1999993              1999994              1999995                    2
                 1999994              1999995              1999996                    2
                 1999995              1999996              1999997                    2
                 1999996              1999997              1999998                    2
                 1999997              1999998              1999999                    2
                 1999998              1999999              2000000                    2
                 1999999              2000000              2000001                    2
                 2000000              2000001              2000002                    2
                      11                                                              2
    
    13 rows selected.
    
    Elapsed: 00:00:02.00
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=359 Card=549946 Byte
              s=9349082)
    
       1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=549946 Bytes=
              9349082)
    
    TEST_ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where ID4!=1;
    
                      ID                  ID1                  ID2                  ID4
    -------------------- -------------------- -------------------- --------------------
                 1999989              1999990              1999991                    2
                 1999990              1999991              1999992                    2
                 1999991              1999992              1999993                    2
                 1999992              1999993              1999994                    2
                 1999993              1999994              1999995                    2
                 1999994              1999995              1999996                    2
                 1999995              1999996              1999997                    2
                 1999996              1999997              1999998                    2
                 1999997              1999998              1999999                    2
                 1999998              1999999              2000000                    2
                 1999999              2000000              2000001                    2
                 2000000              2000001              2000002                    2
                      11                                                              2
    
    13 rows selected.
    
    Elapsed: 00:00:03.06
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9734 Card=549946 Byt
              es=9349082)
    
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=9734 Card=54
              9946 Bytes=9349082)
    
       2    1     BITMAP CONVERSION (TO ROWIDS)
       3    2       BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width