Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Sometimes the development of table structure design, whether the table field is empty is too casual, such as id1 = id2, if the field is allowed to be empty, because the Oracle empty value is not equal to the null value, unexpected results may be obtained. In addition, NULL affects the execution plan of oracle.

The following is a test example where NULL affects the execution plan.

/* 1. Construct the test table. The object_id of the test table created in the create table method is not empty *, and the index/

SELECT Count (*) FROM all_objects WHERE object_id is not null; -- 41790
Drop table test;
Create table test as select * FROM all_objects WHERE object_id is not null; ---- 41791
Create index idx_test ON test (object_id );
Analyze table test compute statistics for table for all indexes for all indexed COLUMNS;
Explain plan for select Count (*) FROM test;
SELECT * from table (dbms_xplan.display );

Plan hash value: 3508397080

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 23 (5) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 |INDEX FAST FULL SCAN| IDX_TEST | 41791 | 23 (5) | 00:00:01 |
--------------------------------------------------------------------------

/* 2. Change the structure of the test table, set the object_id field to NULL, and update the data to NULL/

Alter table test MODIFY object_id number null;
UPDATE test SET object_id = null where rownum = 1;
COMMIT;
Explain plan for select Count (*) FROM test;
SELECT * from table (dbms_xplan.display );
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 135 (2) | 00:00:02 |
| 1 | sort aggregate | 1 |
| 2 |TABLE ACCESS FULL| TEST | 41791 | 135 (2) | 00:00:02 |
-------------------------------------------------------------------

/* 3. Add a condition to the SQL command to filter the NULL data * and use the index/

Explain plan for select Count (*) FROM test WHERE object_id is not null;
SELECT * from table (dbms_xplan.display );
Plan hash value: 3508397080

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 23 (5) | 00:00:01 |
| 1 | sort aggregate | 1 | 4 |
| * 2 |INDEX FAST FULL SCAN| IDX_TEST | 41791 | 23 (5) | 00:00:01 |
----------------------------------------------------------------------------------

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

2-filter ("OBJECT_ID" is not null)

/* 4. delete the modified object_id is NULL data, view the plan, and still go to the full table */

Delete from test WHERE object_id is null;
COMMIT;
Analyze table test compute statistics for table for all indexes for all indexed COLUMNS;
Explain plan for select Count (*) FROM test;
SELECT * from table (dbms_xplan.display );

Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 135 (2) | 00:00:02 |
| 1 | sort aggregate | 1 |
| 2 |Table access full | TEST| 41790 | 135 (2) | 00:00:02 |
-------------------------------------------------------------------

In summary, it seems that if the table structure of the index column is not empty, the index will be taken; if the index list structure can be empty, the whole table will be taken regardless of whether null data exists; if the where index column is not null can be used to filter empty data, the index will still be used.