Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== 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 ===
<code sql>
-- 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

</code>

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?

<code plsql>
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
  
</code>