====== 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