Two ways to let an index contain null values

1. Index a column with a null value with a constant, or a column with a NOT NULL constraint

Create INDEX ind_01 on t01 (col01,1);

Or

Create INDEX ind_01 on t01 (COL01,COL02); --col02 must be with a NOT NULL constraint.

Here is an example:

CREATE TABLE TAB (COL1 number, COL2 number not NULL);
INSERT into TAB SELECT if rownum<=10 THEN NULL ELSE object_id end,object_id from All_objects;

CREATE INDEX TESTX on tab (COL1,COL2);

EXEC Dbms_stats. Gather_table_stats (USER, ' TAB ');

SELECT * from TAB WHERE COL1 is NULL;

Execution Plan
----------------------------------------------------------
Plan Hash value:546044058

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |     1 |     9 | 1 (0) | 00:00:01 |
|* 1 | INDEX RANGE scan|     Testx |     1 |     9 | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-access ("COL1" is NULL)

2. An example of the above null value index, non-null value is not indexed, so that the index will be very small, efficient query.

But when writing SQL, you need to be aware that the predicate condition must be consistent with the function.

Create INDEX ind_01 on t01 (decode (col01,null,1,null));

The first method is suitable for situations in which the other values are indexed, except for null.
The second method applies to indexes that are used only for null values, so this index is very small.

end-

Problem solving with null value query not going to index in field
A little rookie.