Null is Not Null – Filter

While checking something got through below old OTN question, so wanted to verify on optimizer behaviour

https://community.oracle.com/thread/2406366

TEST@XCONTEST>create table test as select RPAD(TO_CHAR(ROWNUM),3,’X’) a,rownum b from dual CONNECT BY LEVEL<=1000; Table created. TEST@XCONTEST>create index test_idx on test (a,b);

Index created.

TEST@XCONTEST>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>’TEST’,CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

PL/SQL procedure successfully completed.

Check for rows with a=”

TEST@XCONTEST>select * from test where a=”;

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------------------------------------
 SQL_ID 1f8m76xwz6mpv, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 766971379

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | TABLE ACCESS FULL| TEST | 1000 | 8000 | 2 (0)| 00:00:01 |
 ---------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)

Optimizer will add filter as null is not null when sub plan tree is false, the sub plan tree will never be executed, that is the reason the cost of parent operation is 0 when the child has 2.

What was the reason for not picking the index and the plan to have the specific filter?

A ZERO length varchar2 is treated as NULL (Thanks to Tom Kyte), so in above query we are searching for null value and nothing equates to null,so the child operation at 2 doesn’t need to be executed at all. Index wasn’t used here as the column a isn’t defined as not null.

TEST@XCONTEST>alter table test modify a not null;

Table altered.

TEST@XCONTEST>select * from test where a=”;

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------
 SQL_ID bzgnr9bg1z2ps, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 1731493281

-----------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -----------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | INDEX FULL SCAN| TEST_IDX | 1000 | 8000 | 1 (0)| 00:00:01 |
 -----------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)
 19 rows selected.