• Standard B*Tree indexes do not store entries for NULL values, which means columns containing NULL values are excluded from the index entirely.
  
  
• Function-based indexes using NVL can include NULL values by converting them to a specific non-NULL value, allowing the index to cover all rows in the table.
  
  
• When creating a function-based index with NVL, the same function must be used in query predicates for the optimizer to utilize the index effectively.
  
  
• The percentage of NULL values in a column and the frequency of IS NULL queries should influence your indexing strategy decisions.
  
  
• Oracle's cost-based optimizer may still choose full table scans over index access when it determines this is more efficient, particularly when data blocks are already cached in the buffer pool.
  

CREATE TABLE table_nvl
(
    id NUMBER(10,0) NOT NULL
,   refid NUMBER(10,0)
);

DECLARE
indexLoop NUMBER(5,0);

BEGIN
FOR indexLoop IN 1..50000 LOOP
    IF MOD(indexLoop,2) = 0 THEN
        INSERT INTO table_nvl (id, refid) VALUES (DBMS_RANDOM.VALUE(1,50000), indexLoop);
    ELSE
        INSERT INTO table_nvl (id, refid) VALUES (DBMS_RANDOM.VALUE(1,50000), NULL);
    END IF;
END LOOP;
END;
/

1. Creating a Standard Index on Non-NULL Column
   The ID column contains no NULL values, so a standard index will include all rows from the table.
   

   CREATE INDEX idx_table_nvl_id ON table_nvl (id);
   ANALYZE INDEX idx_table_nvl_id COMPUTE STATISTICS;
   ANALYZE INDEX idx_table_nvl_id VALIDATE STRUCTURE;
   

   SELECT name, lf_rows FROM index_stats;
   

   Query result:

   | NAME             | LF_ROWS |
   +------------------+---------+
   | IDX_TABLE_NVL_ID | 50000   |
   

   As expected, the index contains 50,000 entries, matching the total number of rows in the table. This index will be effective for queries filtering on the ID column.
2. Creating a Standard Index on Column with NULL Values
   When creating a standard index on the REFID column, which contains approximately 50% NULL values, observe how Oracle handles the NULL entries.
   

   CREATE INDEX idx_table_nvl_refid ON table_nvl (refid);
   ANALYZE INDEX idx_table_nvl_refid COMPUTE STATISTICS;
   ANALYZE INDEX idx_table_nvl_refid VALIDATE STRUCTURE;
   

   SELECT name, lf_rows FROM index_stats;
   

   Query result:

   | NAME                | LF_ROWS |
   +---------------------+---------+
   | IDX_TABLE_NVL_REFID | 25000   |
   

   The index contains only 25,000 entries despite the table having 50,000 rows. This demonstrates that Oracle's B*Tree indexes exclude NULL values entirely. The missing 25,000 entries correspond to the rows where REFID is NULL.
   
   Impact on query execution plans:
   Queries involving NULL value predicates will result in full table scans because the NULL values are not represented in the index.
   

   • Searching for NULL values:

     SELECT * FROM table_nvl WHERE refid IS NULL;
     

     Execution plan:

     | OBJECT_NAME | OPTIONS |
     +-------------+---------+
     | TABLE_NVL   | FULL    |
     

   • Searching for NOT NULL values:

     SELECT * FROM table_nvl WHERE refid IS NOT NULL;
     

     Execution plan:

     | OBJECT_NAME | OPTIONS |
     +-------------+---------+
     | TABLE_NVL   | FULL    |
     

   Even with index hints, NULL value searches cannot utilize the standard index effectively:
   

   • Forced index usage for NULL search:

     SELECT /*+ index(table_nvl idx_table_nvl_refid)*/ * FROM table_nvl WHERE refid IS NULL;
     

     Execution plan:

     | OBJECT_NAME | OPTIONS |
     +-------------+---------+
     | TABLE_NVL   | FULL    |
     

   • Forced index usage for NOT NULL search:

     SELECT /*+ index(table_nvl idx_table_nvl_refid)*/ * FROM table_nvl WHERE refid IS NOT NULL;
     

     Execution plan:

     | OBJECT_NAME         | OPTIONS        |
     +---------------------+----------------+
     | TABLE_NVL           | BY INDEX ROWID |
     | IDX_TABLE_NVL_REFID | FULL SCAN      |
     

   Alternative approach for NOT NULL searches using range predicates:
   

   SELECT * FROM table_nvl WHERE refid > 0;
   

   Execution plan:

   | OBJECT_NAME         | OPTIONS        |
   +---------------------+----------------+
   | TABLE_NVL           | BY INDEX ROWID |
   | IDX_TABLE_NVL_REFID | RANGE SCAN     |
   

   This approach works because it implicitly excludes NULL values and can utilize the index for efficient range scanning.
3. Creating a Function-Based Index with NVL
   Function-based indexes using NVL can solve the NULL value indexing limitation by converting NULL values to a specified non-NULL value, ensuring all rows are included in the index.
   

   DROP INDEX idx_table_nvl_refid;
   CREATE INDEX idx_table_nvl_refid ON table_nvl (NVL(refid,0));
   ANALYZE INDEX idx_table_nvl_refid COMPUTE STATISTICS;
   ANALYZE INDEX idx_table_nvl_refid VALIDATE STRUCTURE;
   

   SELECT name, lf_rows FROM index_stats;
   

   Query result:

   | NAME                | LF_ROWS |
   +---------------------+---------+
   | IDX_TABLE_NVL_REFID | 50000   |
   

   The function-based index now contains all 50,000 rows. NULL values in REFID are converted to 0 and stored in the index, making them searchable.
   
   Note:
   The same NVL function used in the index definition must be used in query predicates for the optimizer to recognize and utilize the index. Standard column references will not match the function-based index.
   
   These queries will result in full table scans because they do not match the index function:
   

   SELECT * FROM table_nvl WHERE refid = 10;
   
   SELECT * FROM table_nvl WHERE refid IS NULL;
   
   SELECT * FROM table_nvl WHERE refid IS NOT NULL;
   
   SELECT * FROM table_nvl WHERE refid > 0; -- IS NOT NULL
   

   Execution plan:

   | OBJECT_NAME | OPTIONS |
   +-------------+---------+
   | TABLE_NVL   | FULL    |
   

   Correct usage with NVL function to utilize the function-based index:
   

   SELECT * FROM table_nvl WHERE nvl(refid,0) = 10;
   SELECT /*+ index(table_nvl idx_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) = 10;
   
   SELECT * FROM table_nvl WHERE nvl(refid,0) = 0; -- IS NULL
   SELECT /*+ index(table_nvl idx_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) = 0; -- IS NULL
   
   SELECT * FROM table_nvl WHERE nvl(refid,0) > 0; -- IS NOT NULL
   SELECT /*+ index(table_nvl idx_table_nvl_refid)*/ * FROM table_nvl WHERE nvl(refid,0) > 0; -- IS NOT NULL
   

   Execution plan:

   | OBJECT_NAME         | OPTIONS        |
   +---------------------+----------------+
   | TABLE_NVL           | BY INDEX ROWID |
   | IDX_TABLE_NVL_REFID | RANGE SCAN     |
   

   The function-based index enables efficient access for all scenarios: searching for specific values, NULL values (converted to 0), and NOT NULL values (greater than 0). This approach provides comprehensive index coverage while maintaining query performance for all possible search patterns on the column.