"equal to NULL" condition in WHERE clause still allows query to execute and use resources

Question

In our PL/SQL code I've found an error, when variable used in WHERE-clause of a query was NULL. So we had a statement in WHERE-clause of a query like "table1.column1 = NULL". I already know about evaluation to UNKNOWN in this case and the query, as expected, didn't return any records.

But "table1" is big and I saw long queries with our mistakenly nulled variable to this table and it indexes in trace. So I've maid some little research an found that queries like:

1.

select *
from table1
where table1.column1 = NULL

2.

select *
from table1
where 1=2

are actually executed. All of them have a correct Plan and normal (as queries with correct data in WHERE clause) execution time. Also this queries consume resources.

The question is: why queries which exactly will not return any result are still executed in Oracle and consume resources?

Answer 1

The optimizer does realize this and will inject a "stop this" filter into the query:

I have a table test with 3 million rows. A simple

select * 
from test;

results in the following execution plan:

----------------------------------------------------------------                 
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)|                 
----------------------------------------------------------------                 
|   0 | SELECT STATEMENT  |      |   3145K|    72M|  3587   (1)|                 
|   1 |  TABLE ACCESS FULL| TEST |   3145K|    72M|  3587   (1)|                 
----------------------------------------------------------------                 

Now lets have a look at the plan for:

select * 
from test
where 1=2;

-----------------------------------------------------------------                
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)|                
-----------------------------------------------------------------                
|   0 | SELECT STATEMENT   |      |      1 |    24 |     0   (0)|                
|*  1 |  FILTER            |      |        |       |            |                
|   2 |   TABLE ACCESS FULL| TEST |   3145K|    72M|  3587   (1)|                
-----------------------------------------------------------------                

Predicate Information (identified by operation id):                              
---------------------------------------------------                              

   1 - filter(NULL IS NOT NULL)                                                  

As you can see Oracle did realize that the WHERE condition will never be true and injected a plan step that essentially prevents the execution of the "TABLE ACCESS FULL" below it. The estimates row of 1

If you retrieve the actual plan that was used at runtime this can be seen as well:

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     1 (100)|      0 |00:00:00.01 |
|*  1 |  FILTER            |      |      1 |        |       |            |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| TEST |      0 |   3145K|    72M|  3587   (1)|      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):                                   
-------------------------------------------------------------                                   

   1 - SEL$1                                                                                    
   2 - SEL$1 / TEST@SEL$1                                                                       

Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             

   1 - filter(NULL IS NOT NULL)                                                                 

You can see the "A-Rows" (="actual rows") for all plan steps including the "TABLE ACCESS FULL" to be zero, which means that data was never read from the table.

---

Note that this also happens for conditions that aren't that obvious, but can be deduced from constraints to be never true. The id column of my test table is the primary key and thus it is NOT NULL

When you run:

select *
from test
where id is null;

The optimizer knows that this is a condition that is never true (because of the NOT NULL constraint) and produces the same "stop this" plan as before:

-----------------------------------------------------------------                
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)|                
-----------------------------------------------------------------                
|   0 | SELECT STATEMENT   |      |      1 |    24 |     0   (0)|                
|*  1 |  FILTER            |      |        |       |            |                
|   2 |   TABLE ACCESS FULL| TEST |   3145K|    72M|  3587   (1)|                
-----------------------------------------------------------------                

Predicate Information (identified by operation id):                              
---------------------------------------------------                              

   1 - filter(NULL IS NOT NULL)                                                  

Answer 2

Probably the optimizer does not expect users to write senseless queries, and therefore doesn't check for it.

Note that It would cost extra time every time to check for a senseless query, and the majority of cases would have no gain from it.
Why punish gazillions of queries with extra effort, to simply avoid a load for bad queries? It seems correct to punish the writer of the useless query, instead of all others to please him.