"Go" Oracle index column NULL value raises execution plan test sample for this table

Sometimes the development of the table structure design, is too random to table field is empty, such as ID1=ID2, if the Allowed field is empty, because the Oracle hollow value is not equal to null value, it is possible to get unexpected results. Beyond that, the key is that null affects Oracle's execution plan.

The following null affects the test example for the execution plan.

/*1. Build test table, where create table is constructed with the test sheet structure object_id non-empty *, go index/

SELECT Count (*) from all_objects WHERE object_id are not NULL; --41790 Pen
DROP TABLE test;
CREATE TABLE Test as SELECT * from All_objects WHERE object_id are not NULL; ----41791 Pen
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, make the object_id field null, and update a data for null*, go to the full table/

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. Adding conditional filters to SQL instructions NULL data *, GO index/

EXPLAIN PLAN for SELECT Count (*) from Test WHERE object_id are 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 |    163k| 23 (5) | 00:00:01 |
----------------------------------------------------------------------------------

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

2-filter ("object_id" is not NULL)

/*4. Delete the object_id is null data from the above changes, then view plan, still walk the whole 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 conclusion, it seems that if the table structure of the index column is non-empty, the index will go, if the index list structure is empty, then the whole table will be gone regardless of the existence of NULL data, or the index can be filtered by the where index column is not NULL to filter the empty data.

Transferred from: http://www.linuxidc.com/Linux/2012-09/69938.htm

"Go" Oracle index column NULL value raises execution plan test sample for this table