Bei einer Anwendung stellte sich mir die Aufgabe, eine Abfrage zu optimieren, ohne die Abfrage oder die Tabelleninhalte ändern zu können. Wesentliches Kriterium der WHERE Klause war eine Abfrage auf der Spalte FELDNUM der Form (FELDNUM IS NULL or FELDNUM >= 5). Ich versuchte einen Index zu nutzen, der auch die NULL Werte der Spalte FELDNUM enthält.

Leider werden in diesem Beispiel sehr viele DML Operationen auf die Tabelle angewendet. Zudem ist auch die Selektivität der sehr hoch. Zwei Gründe, die gegen einen Einsatz eines Bitmap Indexes sprachen.

Deshalb versuchte ich es mit einem „normalen“ B*Tree Index. Sobald man kombinierte, also aus mindestens zwei oder mehr Spalten zusammengesetzte Indizes verwendet, werden für die erste Spalte auch NULL Werte im Index gespeichert.

Um dies zu überprüfen, habe ich mir den Index in ein Tracefile schreiben lassen. Dazu sind die FILE_ID und der Start- und Ende-Block zu ermitteln. Diese Daten habe ich wie folgt ermittelt, wenn der Schemaowner = „RHARTWIG“ und der Indexname = „INDEXTST“ sind:

1
2
3
4
5
6

SELECT file_id, block_id AS STARTBLCK, block_id + blocks AS ENDBLCK
FROM DBA_EXTENTS 
WHERE owner='RHARTWIG'
AND segment_name='INDEXTST'
AND segment_type='INDEX'
;

Eine mögliche Ausgabe wäre dann

   FILE_ID  STARTBLCK    ENDBLCK
---------- ---------- ----------
         4     405144     405152

Mit der Anweisung

ALTER system dump datafile 4 block MIN 405144 block MAX 405152;

läßt sich dann ein Tracefile mit dem Index erstellen. Öffnet man dieses und sucht nach „Leaf block dump“, so landet man weit hinten im Tracefile. Hier kann man dann deutlich erkennen, ob NULL Werte gespeichert sind. Hier ein exemplarischer Auszug eines Indexes mit zwei Spalten, bei denen die ersten zwei, dargestellten Zeilen NULL Werte enthalten – dieses sind die Zeilen 2 bis 5 im Index:

ROW#2[7997] flag: ------, lock: 0, len=13
col 0; len 2; (2):  c1 04
col 1; NULL
col 2; len 6; (6):  01 06 2e 8b 00 02
ROW#3[7984] flag: ------, lock: 0, len=13
col 0; len 2; (2):  c1 05
col 1; NULL
col 2; len 6; (6):  01 06 2e 8b 00 03
ROW#4[7964] flag: ------, lock: 0, len=20
col 0; len 2; (2):  c1 06
col 1; len 7; (7):  78 72 0b 0d 0f 02 12
col 2; len 6; (6):  01 06 2e 8b 00 04
ROW#5[7944] flag: ------, lock: 0, len=20
col 0; len 2; (2):  c1 07
col 1; len 7; (7):  78 72 0b 0d 0f 02 17
col 2; len 6; (6):  01 06 2e 8b 00 05

Um nun zu testen, ob nun auch der Index genutzt wird, habe ich für das entsprechende SQL den Ausführungsplan erstellt. In dem vorliegenden Fall wurde der Index genutzt.

Um aber nun allgemein mehr darüber zu untersuchen, legte ich drei verschiedene Tabellen an:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- TEST TABLE 1
--
CREATE TABLE indextst(nu NUMBER, word VARCHAR2(100 CHAR))
;
INSERT INTO indextst 
SELECT decode(MOD(rownum,3),0,NULL,MOD(rownum,3))
    ,  TO_CHAR (TO_DATE (rownum, 'j'), 'jsp') 
FROM dual 
CONNECT BY level < 1000000;
commit;
CREATE INDEX idx_col1 ON indextst(nu);
CREATE INDEX idx_col1_col2 ON indextst(nu, word);
 
-- TEST TABLE 2
--
CREATE TABLE indextst2(nu NUMBER, word VARCHAR2(100 CHAR))
;
INSERT INTO indextst2 
SELECT decode(MOD(rownum,100),0,NULL,MOD(rownum,100))
    ,  TO_CHAR (TO_DATE (rownum, 'j'), 'jsp') 
FROM dual 
CONNECT BY level < 1000000;
commit;
CREATE INDEX idx2_col1 ON indextst2(nu);
CREATE INDEX idx2_col1_col2 ON indextst2(nu, word);
 
-- TEST TABLE 3
--
CREATE TABLE indextst3(nu NUMBER, word VARCHAR2(100 CHAR))
;
INSERT INTO indextst3
SELECT decode(MOD(rownum,10000),0,NULL,MOD(rownum,10000))
    ,  TO_CHAR (TO_DATE (rownum, 'j'), 'jsp') 
FROM dual 
CONNECT BY level < 1000000; 
commit; 
CREATE INDEX idx3_col1 ON indextst3(nu); 
CREATE INDEX idx3_col1_col2 ON indextst3(nu, word); 
 
-- Anlegen der Statistiken 
-- 
EXEC dbms_stats.GATHER_SCHEMA_STATS(ownname=>'RH', ESTIMATE_PERCENT=>100, CASCADE=>TRUE);

In keinem dieser Fälle wurde bei einer Abfrage der Form

1

SELECT * FROM TAB WHERE NU IS NULL OR NU = 1

ein Index genutzt.

Nach einem Ändern der Tabellen

1
2
3

ALTER TABLE indextst MODIFY WORD NOT NULL;
ALTER TABLE indextst2 MODIFY WORD NOT NULL;
ALTER TABLE indextst3 MODIFY WORD NOT NULL;

wurde für die beiden letzten Tabellen der Index IDX2_COL1_COL2 resp. IDX3_COL1_COL2 genutzt.
Nur bei der Tabelle indextst wurde kein Index genutzt, da die Selektivität für die Spalte NU im Vergleich zur Tabellengröße zu klein war.

FAZIT:

• kombinierte Indizes enthalten NULL Werte, sofern wenigstens eine Spalte immer NOT NULL ist
• damit ein solcher Index auch genutzt werden kann, um Bedingungen der Form (FELDNUM IS NULL) aufzulösen, muss wenigstens eine weitere Spalte als NOT NULL deklariert sein
• auch muss die Selektivität hinreichend gut sein, damit der Index überhaupt genutzt wird – analog zu Bedingungen auch ohne IS NULL