All Oracle specialists know that a predicate X=NULL can never be true and we should use “X is NULL” in such cases. The Oracle optimizer knows about that, so if we create a table like this:
create table tnulls
as
select
level as N
,case when mod(level,10)=1 then level
else null
end as A
from dual
connect by level<=1000000;
create index ix_a on tnulls(a desc);
create index ix_an on tnulls(A, N);
In case of simple A=NULL:
select 1 from tnulls where A=null
Plan hash value: 3333700597
--------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)|
|* 1 | FILTER | | 1 | | |
| 2 | TABLE ACCESS FULL| TNULLS | 0 | 1000K| 444 (2)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
CBO adds filter(NULL IS NOT NULL), so it doesn’t even start the child operation TABLE ACCESS FULL. And even in the case of using a NULL bind variable – “A=:varnull” with FTS or IRS, we can see that Oracle doesn’t read data blocks:
create index ix_an on tnulls(A, N);
alter session set statistics_level=all;
SQL> select/*+ no_index(tnulls) */ 2 from tnulls where A=:varnull;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','typical allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 4xf5hr7mm4zvz, child number 0
-------------------------------------
select/*+ no_index(tnulls) */ 2 from tnulls where A=:varnull
Plan hash value: 3133871912
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 446 (100)| | 0 |00:00:00.01 |
|* 1 | TABLE ACCESS FULL| TNULLS | 1 | 1 | 2 | 446 (2)| 00:00:01 | 0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=:VARNULL)
SQL> select/*+ index(tnulls ix_an) */ 3 from tnulls where A=:varnull;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','typical allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID b5qz9yv13tyr8, child number 0
-------------------------------------
select/*+ index(tnulls ix_an) */ 3 from tnulls where A=:varnull
Plan hash value: 404583077
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 |
|* 1 | INDEX RANGE SCAN| IX_AN | 1 | 1 | 2 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=:VARNULL)
But take a look what happens if we create a descending index: index ix_a_desc on tnulls(a desc):
SQL> create index ix_a_desc on tnulls(a desc);
SQL> select/*+ index(tnulls ix_a_desc) */ 4 from tnulls where A=:varnull;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','typical allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 8n09rudu2pr4j, child number 0
-------------------------------------
select/*+ index(tnulls ix_a_desc) */ 4 from tnulls where A=:varnull
Plan hash value: 3186386750
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.06 | 1633 | 1632 |
|* 1 | INDEX RANGE SCAN| IX_A_DESC | 1 | 0 |00:00:00.06 | 1633 | 1632 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TNULLS"."SYS_NC00003$"=SYS_OP_DESCEND(:VARNULL))
filter(SYS_OP_UNDESCEND("TNULLS"."SYS_NC00003$")=:VARNULL)
Many of us know that descending indexes are FBIs (function-based index), but it appears not everyone knows that SYS_OP_DESCEND(NULL) returns 0x00 (RAW data type), so such indexes contain NULLs. This is why IRS scans all those index blocks by access predicates and filters out all satisfied rows by FILTER predicate (SYS_OP_UNDESCEND(VirtCol) = :VARNULL).
Originally, I got a question with a plan and statistics from tkprof output:
SELECT/*+ index(T IDX_AB) */ * FROM T where A=:B1 and B=:B2;
We have an index on T(A,B), where each pair of (A,B) is nearly unique, so why is index access using that index so slow?
Upon examining this, I immediately noticed SYS_OP_DESCEND/SYS_OP_UNDESCEND in the predicate section and requested a raw trace to verify the bind variables. Once I received it, I discovered that over 90% of them were, in fact, NULLs. Since they don’t really need to return any rows in case of NULLs, the issue was solved with a simple rewrite:
SELECT/*+ index(T IDX_AB) */ * FROM T where A=:B1 and B=:B2;
to
SELECT/*+ index(T IDX_AB) */ * FROM T where A=:B1 and B=:B2
and :B1 is not null and :B2 is not null;
In conclusion, even seemingly simple aspects like predicates with NULLs can have significant nuances when dealing with unusual scenarios, such as descending indexes. Being aware of these subtleties can help you better understand the inner workings of Oracle indexes and optimize query performance in more complex situations.