Fastest way of testing for a NULL field (null detection)

Question

I use two different ways of checking whether a database field column_b is not NULL. Which is faster and why?

First query

SELECT * FROM my_table
WHERE column_b IS NOT NULL;

Second query

SELECT * FROM my_table
WHERE column_b = column_b;

There is no index on column_b.

Answer 1

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.

By the way, this is such an interesting question and optimisation type, I've blogged about it and about similar optimisations more in depth here.

Answer 2

They are not always the same if you are using Objects:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE coord AS OBJECT (
  x NUMBER,
  y NUMBER,
  ORDER MEMBER FUNCTION match (l coord) RETURN INTEGER
);
/
CREATE TYPE BODY coord AS 
  ORDER MEMBER FUNCTION match (l coord) RETURN INTEGER IS 
  BEGIN 
    RETURN ( x   * 100 + y )
         - ( l.x * 100 + l.y);
  END;
END;
/

CREATE TABLE table_name ( col1, col2 ) AS
  SELECT 1, coord( 2, 3 ) FROM DUAL UNION ALL
  SELECT 2, coord( 0, 2 ) FROM DUAL UNION ALL
  SELECT 3, coord( NULL, 2 ) FROM DUAL UNION ALL
  SELECT 4, NULL FROM DUAL
/

Query 1:

SELECT *
FROM   table_name
WHERE  col2 IS NOT NULL

Results:

| COL1 |                       COL2 |
|------|----------------------------|
|    1 | oracle.sql.STRUCT@10394576 |
|    2 | oracle.sql.STRUCT@11ddf9d5 |
|    3 | oracle.sql.STRUCT@595e9c7d |

Explain Plan:

 Plan Hash Value  : 3383972830 

---------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    3 |   123 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | TABLE_NAME |    3 |   123 |    3 | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(SYS_OP_NOEXPAND("COL2") IS NOT NULL)

Query 2:

SELECT *
FROM   table_name
WHERE  col2 = col2

Results:

| COL1 |                       COL2 |
|------|----------------------------|
|    1 | oracle.sql.STRUCT@35c56f09 |
|    2 | oracle.sql.STRUCT@1f0e893f |

Explain Plan:

 Plan Hash Value  : 3383972830 

---------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    1 |    41 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | TABLE_NAME |    1 |    41 |    3 | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("COORD"."MATCH"("COL2","COL2")=0)