Take care with NOT IN Oracle

Below data copied from (http://jonathanlewis.wordpress.com/2007/02/25/not-in/)



The “NOT IN” operator can cause confusion – especially when you have nulls lurking in critical columns, and most particularly where subqueries are involved

You can avoid most of the problems if you just remember that “NOT IN” is not the opposite of “IN”. Here’s an example to explain why.

colX in (value1, value2, value3)
means

      colX = value1
or    colX = value2
or    colX = value3
When Oracle is evaluating this predicate, it only takes one of the three components to evaluate to true for the entire statement to evaluate to true.

colX NOT in (value1, value2, value3)
means

      colX != value1
AND   colX != value2
AND   colX != value3
When Oracle is evaluating this predicate, every single one of the components must evaluate to true for the entire predicate to evaluate to true.

But if one of the values involved is a NULL, then that component evaluates to neither true nor false, it evaluates to null – so the whole expression evaluates to null.

So NULL values are allowed and don’t affect the outcome of IN predicates, but do affect the outcome of “NOT IN” predicates. You’ll also notice that in general IN predicates can short-circuit – the tests can stop on the first success (true), but the same is not true for NOT IN. [But see comment 2 and comment 4 below.

ORA-08103: Object No Longer Exists

Finally I am able to find the root cause for ORA-08103: object no longer exists.

Root Cause Analyses:-
       During executing of select statement if any other session has truncated the underling table used     in select statement ,we got the error ORA-08103: object no longer exists.
 Example :-   Procedure PROC_1 ties to insert into table TABLE_1  which is refers  table TABLE_2.
                      At the same time some other refresh has truncated the table TABLE_2.

Solution:-    As a permanent solution, We should keep all the dimension as merge NOT Truncate/Insert.