Index Verwendung von "is null" Prädikat optimieren

Problem: Ist ein „normaler“ Index auf einer Spalte angelegt, kann dieser für eine „is null“ Abfrage nicht verwendet werden.

Lösung: Index mit konstanten Wert für die Null Columns anlegen, dann wird dieser Index verwendet.

Beispiel

-- Testdaten
CREATE TABLE t AS SELECT * FROM all_objects;
ALTER TABLE t ADD (index_col varchar2(10);
 
-- Index mit einer Spalte
CREATE INDEX idx_t_null_column ON t(index_col);
 
-- Abfrage:
SELECT COUNT(*) FROM t WHERE index_col IS NULL;
---------------------------------------------------------------------------
| Id  | Operation          | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |   227   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 56375 |   385K|   227   (1)| 00:00:03 |
---------------------------------------------------------------------------
-- Index wird nicht verwendet
 
-- Index löschen und neu anlegen
DROP INDEX idx_t_null_column;
 
-- Mit Konstanten Wert anlegen
 
CREATE INDEX idx_t_null_column ON t(index_col,-1);
 
---
-- abfragen
--
SELECT COUNT(*) FROM t WHERE index_col IS NULL;
---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | ROWS  | Bytes | Cost (%CPU)| TIME     |
 
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |     7 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                   |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_NULL_COLUMN | 56375 |   385K|     8   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
-- Index wird verwendet

Im Beispiel geht es um die Index Verwendung, ob nun die Performace besser geworden ist, sei dahin gestellt!

Kosten

Was kostet das aber in der DB?

CREATE INDEX idx_t_null_column ON t(index_col);
 
exec DBMS_STATS.GATHER_INDEX_STATS('GPI','IDX_T_NULL_COLUMN');
 
-- Einträge
SELECT NUM_ROWS,BLEVEL,DISTINCT_KEYS FROM USER_INDEXES WHERE index_name='IDX_T_NULL_COLUMN';
NUM_ROWS  BLEVEL      DISTINCT_KEYS
---------- ---------- -------------
0          0             0
 
-- Größe         
SELECT bytes,BLOCKS FROM user_segments WHERE segment_name='IDX_T_NULL_COLUMN';
BYTES     BLOCKS
---------- ----------
65536          8         
 
----------------------------------------------
 
-- Mit Konstanten Wert anlegen
 
CREATE INDEX idx_t_null_column ON t(index_col,-1);
 
exec DBMS_STATS.GATHER_INDEX_STATS('GPI','IDX_T_NULL_COLUMN');
 
-- Einträge
SELECT NUM_ROWS,BLEVEL,DISTINCT_KEYS FROM USER_INDEXES WHERE index_name='IDX_T_NULL_COLUMN';
NUM_ROWS   BLEVEL     DISTINCT_KEYS
---------- ---------- -------------
 55625     1           1
 
-- Größe
 
SELECT bytes,BLOCKS FROM user_segments WHERE segment_name='IDX_T_NULL_COLUMN';
BYTES     BLOCKS
---------- ----------
2097152        256
 
-- Optimieren -- weniger Bytes für Konstante verwenden!
 
GPI@GPI-?>CREATE INDEX idx_t_null_column ON t(index_col,'A');
 
INDEX wurde erstellt.
 
GPI@GPI-?>SELECT bytes,BLOCKS FROM user_segments WHERE segment_name='IDX_T_NULL_COLUMN';
 
     BYTES     BLOCKS
---------- ----------
    983040        120