MIN- oder MAX- Bildung per Index - Lambertz

Lambertz
onsulting
________________________________________________________________________________________________
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
E-Mail: [email protected]
Internet: http://www.lambertz-consulting.de
MIN oder MAX Bildung per B*Tree Index Hint
Zugegeben, der Trick Min- oder Maximalwerte per Index Hint zu ermitteln ist nicht neu.
Gewöhnlich sind mit diesem Trick Laufzeitverbesserungen zu erzielen, jedoch nicht
unbedingt. Aber wann lohnt sich der Einsatz und unter welchen Umständen ist eine
Verschlechterung der Laufzeit zu erwarten?
Folgende Tabelle liegt vor..
SQL > DESC adressen;
Name
Null?
------------------------------- -------ANREDE
VORNAME
NAME
STRASSE
PLZ
ORT
TELEFON
VORWAHL
RUFNUMMER
Type
---VARCHAR2(10)
VARCHAR2(25)
VARCHAR2(90)
VARCHAR2(60)
NUMBER(5)
VARCHAR2(60)
VARCHAR2(15)
NUMBER(10)
VARCHAR2(30)
..mit folgendem Index:
SQL > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM all_ind_columns
WHERE index_name = 'ADRESSEN_IDX01';
TABLE_NAME
-----------------------------ADRESSEN
ADRESSEN
COLUMN_NAME
COLUMN_POSITION
-------------- --------------PLZ
1
VORWAHL
2
Die Abfrage nach der größten PLZ für die Vorwahl „07774“ lautet:
SQL > SELECT MAX( plz )
FROM adressen
WHERE vorwahl = 7774;
MAX(PLZ)
---------78267
1 row selected.
real: 3426
_____________________________________________________________________________________
MIN- oder MAX- Bildung per Index
Seite 1
Lambertz
onsulting
________________________________________________________________________________________________
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
E-Mail: [email protected]
Internet: http://www.lambertz-consulting.de
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=7 Optimizer=CHOOSE
1
0
SORT (AGGREGATE)
2
1
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Statistics
---------------------------------------------------------0 recursive calls
10 db block gets
4405 consistent gets
Um die größte PLZ zu ermitteln, nutzt Oracle den Index ADRESSEN_IDX01. Auf den
ersten Blick nicht schlecht.
Die Anzahl CONSISTENT GETS verraten jedoch, daß 4405 Datenblöcke herangezogen
wurden, die maximale PLZ zu ermitteln. Ursache ist der FAST FULL SCAN des Indizes.
Unter dem Aspekt, daß der Index aufsteigend sortiert vorliegt, befindet sich die größte
PLZ im letzten Eintrag, welcher der WHERE Bedingung entspricht. Es ist also nicht
erforderlich den gesamten Index zu lesen. Dies wird erreicht durch folgendes Statement:
SQL > SELECT /*+ INDEX_DESC( adressen adressen_idx01 ) */
2
plz
3
FROM adressen
4
WHERE vorwahl = 7774
5
AND ROWNUM =1;
PLZ
---------78267
1 row selected.
real: 200
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=26 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
INDEX (FULL SCAN DESCENDING) OF 'ADRESSEN_IDX01'
Statistics
---------------------------------------------------------8 recursive calls
0 db block gets
251 consistent gets
_____________________________________________________________________________________
MIN- oder MAX- Bildung per Index
Seite 2
Lambertz
onsulting
________________________________________________________________________________________________
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
E-Mail: [email protected]
Internet: http://www.lambertz-consulting.de
Mit dem Hint INDEX_DESC wird der Index vom Ende her gelesen. ROWNUM=1
bestimmt, daß der Scanvorgang beendet wird, sobald der erste Eintrag im Index der
WHERE Bedingung (VORWAHL=7774) entspricht.
An dieser Stelle erfolgt ein FULL SCAN statt eines FAST FULL SCAN und ist ein
schlechterer Indexzugriff. Jedoch fällt die Anzahl CONSISTENT GETS mit 251
wesentlich geringer aus und die Laufzeit liegt mit 200 ms auch besser.
Nun, welchen Laufzeitgewinn bringt der Einsatz eines entsprechenden Hint für das
Ermitteln der kleinsten PLZ einer VORWAHL. Zu Beginn wieder der Einsatz der
entsprechenden Funktion.
SQL >
2
3
SELECT MIN( plz )
FROM adressen
WHERE vorwahl = 7774;
MIN(PLZ)
---------78267
1 row selected.
real: 3352
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1053 Optimizer=CHOOSE
1
0
SORT (AGGREGATE)
2
1
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Statistics
---------------------------------------------------------0 recursive calls
4 db block gets
4405 consistent gets
Zeitlich ist kaum ein Unterschied zum Einsatz der MAX Funktion zu erkennen. Die
Anzahl CONSISTENT GETS ist für die MIN und MAX Bildung in beiden Fällen identisch.
Ursache ist, daß der Index in beiden Fällen komplett gescannt wurde.
Das Select Statement mit entsprechendem Hint lautet nun:
SQL >
2
3
4
SELECT /*+ INDEX_ASC( adressen adressen_idx01 ) */
plz
FROM adressen
WHERE vorwahl = 7774
_____________________________________________________________________________________
MIN- oder MAX- Bildung per Index
Seite 3
Lambertz
onsulting
________________________________________________________________________________________________
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
E-Mail: [email protected]
Internet: http://www.lambertz-consulting.de
5
AND ROWNUM =1;
PLZ
---------78267
1 row selected.
real: 4989
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=4397 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
INDEX (FULL SCAN) OF 'ADRESSEN_IDX01'
Statistics
---------------------------------------------------------0 recursive calls
0 db block gets
4170 consistent gets
Die Laufzeit hat sich gegenüber der MIN Funktion verschlechtert, obwohl die Anzahl
CONSISTENT GETS geringer ausfallen –wenn auch nicht wesentlich.
Für das Emitteln des Maximalwertes wurden 251 Blöcke benötigt und für den
Minimalwert waren 4170 Blöcke erforderlich.
SQL > SELECT /*+ INDEX_FFS( adressen adressen_idx01 ) */
2
plz
3
FROM adressen
4
WHERE vorwahl = 7774
5
AND ROWNUM =1;
PLZ
---------78267
1 row selected.
real: 3042
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Cost=1053 Optimizer=CHOOSE
1
0
COUNT (STOPKEY)
2
1
INDEX (FAST FULL SCAN) OF 'ADRESSEN_IDX01'
Statistics
_____________________________________________________________________________________
MIN- oder MAX- Bildung per Index
Seite 4
Lambertz
onsulting
________________________________________________________________________________________________
Rainer Lambertz, Kreuzstraße 54, 41564 Kaarst,
E-Mail: [email protected]
Internet: http://www.lambertz-consulting.de
---------------------------------------------------------0 recursive calls
4 db block gets
4159 consistent gets
Da standardmäßig der Index ASC gelesen wird, kann der Minimalwert über den Hint
INDEX_FFS erfolgen, weil FAST FULL SCAN einfach schneller das Ergebnis
bestimmen kann.
Die Summe der CONSISTENT GETS für die Min und Max Bildung per Index Hint, liegt
mit 4421 (bzw. 4410 für den FAST FULL SCAN) recht dicht an den 4405 Datenblöcken,
die benötigt wurden den gesamten Index zu scannen. Darin liegt die Ursache für die
unterschiedlichen Laufzeiten bei der Ermittlung der MIN und MAX Werte per Index. Der
Maximalwert lag weit vorne im Index und somit mußte gesamte Rest des Index (von
hinten nach vorne) gelesen werden um die minimale PLZ zu bestimmen. Beiläufig ist
erkennbar, daß ein B*tree Index tatsächlich über eine rückwärtige Verkettung aller
Indexeinträge verfügt.
_____________________________________________________________________________________
MIN- oder MAX- Bildung per Index
Seite 5