SQL conditions - use of NOT

[DreamWarrior]

Originally posted by DaPi
Where do you get that from?

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.