Ersin Gülbahar Ersin Gülbahar - 1 month ago 9
SQL Question

Which statement is more optimized?

I use 2 statements for one column value is not equal to

null


Which is faster and why?

First query



select * from my_table
where columnb is not null;


Second query



select * from my_table
where columnb = columnb ;


There is no index on column.

Answer Source

For standard, scalar data types, they're both the same on Oracle (I tried 12c and 11g), as you get two times the same execution plan. (see MT0's answer for an edge case when this isn't true)

Proof:

CREATE TABLE my_table (columnb NUMBER);

EXPLAIN PLAN FOR
SELECT *
FROM my_table
WHERE columnb IS NOT NULL;

SELECT *
FROM TABLE (dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT *
FROM my_table
WHERE columnb = columnb;

SELECT *
FROM TABLE (dbms_xplan.display);

In both cases, I'm getting:

Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("COLUMNB" IS NOT NULL)

Now, even if you did add an index....

CREATE INDEX my_index ON my_table (columnb);

... you'd still get the same plan for both queries:

Plan hash value: 887433238

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | MY_INDEX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("COLUMNB" IS NOT NULL)

What if the column was NOT NULL?

Let's try this:

DROP INDEX my_index; -- Get back to the initial situation
ALTER TABLE my_table MODIFY columnb NUMBER NOT NULL;

The plans I'm getting now are these, with the entire predicate having been eliminated, in both cases:

Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MY_TABLE |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Conclusion

Since you don't get any advantage from the "clever" approach, simply don't do it and write the IS NOT NULL predicate to be more clear.