CBO: Überflüssige Prädikate

Ein recht wichtiger Hinweis von Jonathan Lewis zum Thema redundanter und nutzloser Prädikate: wenn der CBO erkennt, dass ein Prädikat notwendigerweise FALSE ist bzw. durch ein anderes Prädikat bedingt ist, dann kann er dieses Prädikat ignorieren. Das Beispiel dazu lautet:

I can only make assumptions about how the optimizer code is handling this predicate - but if we assume that it simplifies predicates when possible then that's sufficient to explain the disappearance of anything that looks like "125 is null". Consider: 

SQL> select * from t1 where n1 > 14 and n1 > 15;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2774 |   506K|    14 |
|*  1 |  TABLE ACCESS FULL| T1   |  2774 |   506K|    14 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1">15)

Has "n1 > 14" disappeared, or has the compound predicate "n1 > 14 and n1 > 15" been simplified.

Your predicate simplifies to "col = :b or FALSE" which simplifies to "col = :b".

The only time that FALSE (which newer versions of Oracle represent by "NULL IS NOT NULL") is visible as a final predicate is when it is the ONLY predicate on a table (as in select user from dual where 1 = 0).

Wie der Herr Lewis erklärt: es ist nur eine Annahme. Aber eine sehr plausible.