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?

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

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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:

Quote:

---

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

Quote:

---

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

Quote:

---

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?

Quote:

---

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.

It seems intuitively correct that if you have a large table having a highly-skewed, indexed, histogram-analyzed column of values "A" and "B", like ...

Code:

---

select my_col,count(*)
from my_table
group by my_col;

MY_COL COUNT(*)
------ --------
A      99999999
B      1

---

.. then Oracle would use the index for predicates like ...

MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'

... and would use FTS for predicates like ...

MY_COL='A'
... or ...
MY_COL!='B'

Unfortunately, I haven't been able to put together a test case to demonstrate it, so maybe theory don't apply here.

Actually, rethinking it i bet that the != would be much more likely to use an index in a star transformation, involving bitamp index merges etc.

Quote:

---

Originally posted by slimdave

Code:

---

select my_col,count(*)
from my_table
group by my_col;

MY_COL COUNT(*)
------ --------
A      99999999
B      1

---

.. then Oracle would use the index for predicates like ...

MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'

... and would use FTS for predicates like ...

MY_COL='A'
... or ...
MY_COL!='B'

---

I doubt only for that highlighted part...

PS

Code:

---

TEST:ABHAY> Set trimspool on
TEST:ABHAY> analyze table test compute statistics;

Table analyzed.

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  select
  2    INDEX_OWNER    ,
  3    INDEX_NAME      ,
  4    TABLE_OWNER    ,
  5    TABLE_NAME      ,
  6    COLUMN_NAME    ,
  7    COLUMN_POSITION
  8* from dba_ind_columns where index_name='AK_TEST'
TEST:ABHAY> /

INDEX_OWNER                    INDEX_NAME                    TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
COLUMN_NAME
-----------------------------------------------
    COLUMN_POSITION
--------------------
ABHAY                          AK_TEST                        ABHAY                          TEST
ID4
                  1

ABHAY                          AK_TEST                        ABHAY                          TEST
ID2
                  2

TEST:ABHAY> set autotrace on explain
TEST:ABHAY> alter session set optimizer_mode=ALL_ROWS;

Session altered.

TEST:ABHAY> select ID4, Count(*) from TEST group by ID4;

                ID4            COUNT(*)
-------------------- --------------------
                  1              1099799
                  2                  13


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1673 Card=2 Bytes=
          4)

  1    0  SORT (GROUP BY) (Cost=1673 Card=2 Bytes=4)
  2    1    TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1099812 Byt
          es=2199624)


TEST:ABHAY> select * from test where id4=2 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
            1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=18)
  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Byt
          es=18)

  2    1    BITMAP CONVERSION (TO ROWIDS)
  3    2      BITMAP INDEX (SINGLE VALUE) OF 'AK_TEST'


TEST:ABHAY> select * from test where id4!=1 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
            1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=359 Card=1 Bytes=1
          8)

  1    0  TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1 Bytes=18)

You can see its going for FTS with ID4!=1

TEST:ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where id4!=1 and id2=1999991;

                  ID                  ID1                  ID2                  ID4                  ID5
-------------------- -------------------- -------------------- -------------------- --------------------
            1999989              1999990              1999991                    2                    1


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9734 Card=1 Bytes=
          18)

  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=9734 Card=1
          Bytes=18)

  2    1    BITMAP CONVERSION (TO ROWIDS)
  3    2      BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
compare the costs with index scan and without for != condition

---

!= condition will always call for FULL scans (Table/Index), and oracle feels FULL Table scan is wiser than FULL Index scan, as you know FTS can fetch DFMC blocks in one I/O which FULL index scan dosent..

Ok, only exception will be if Index blocks is much less than Table blocks consumed. I would guess a Table with high number COLS present and indexed on only few COLS, than Index Scan would be wiser.

Abhay.