Attribute Clustering, ein weithin unterschätztes Feature der Oracle-Datenbank (I)
Auch in Zeiten von Cloud und skalierbaren Ressourcen kann das physische Design immer noch eine entscheidende Rolle spielen, wenn es um die Effizienz einer datenbankgestützten Applikation geht.
Oracle hat mit der Version 12c ein weniger beachtetes Feature eingeführt, das es erlaubt, die physische Organisation der in sogenannten "Heap"-Tabellen gespeicherten Daten aktiv zu beeinflussen und damit die Effizienz und Performance bestimmter Zugriffswege auf die Daten zu optimieren, was sehr signifikante Auswirkungen auf unterschiedliche Aspekte haben kann – nicht nur die Performance von Abfragen, sondern unter anderem auch der eventuelle Kompressionsfaktor der Daten kann positiv beeinflusst werden.
Um was geht es beim "Attribute Clustering"?
Standardmäßig verwendet Oracle bei Tabellenobjekten den Typ Heap – wenn nicht anders angegeben. Heap-Tabellen heißen so, da sie keiner vorgegebener Organisationsform folgen – die Daten werden als unorganisierter "Haufen" verwaltet und es gilt die einfache Regel, dass neu einzufügende Zeilen dort abgelegt werden, wo die Datenbank in der Tabelle Platz findet. Das macht das Schreiben von Daten extrem effizient, da nur eine grundlegende Freiplatzverwaltung benötigt wird – und erlaubt sogar Spezialformen beim Schreiben von neuen Datensätzen wie den sogenannten "APPEND"-Modus, bei dem eine Session exklusiv neue Datensätze oberhalb der aktuellen Obergrenze (HighWaterMark – HWM) anlegt – was das Schreiben noch effizienter machen kann: Da die Daten nicht in bereits existierende Blöcke geschrieben werden, kann sich die Datenbank sowohl den Zugriff auf die Freiplatzverwaltungsinformation dieser Blöcke sparen als auch die sonst notwendige Veränderungsprotokollierung im Undo-Tablespace zum Rückgängig machen (Undo-Information) – andere Sessions sehen die neu geschriebenen Daten vor einem COMMIT einfach noch nicht, da ja oberhalb der aktuellen HWM platziert, benötigen also auch keine Undo-Information, um Veränderungen an existierenden Blöcken aus Lesekonsistenzgründen eventuell rückgängig zu machen. Das eventuelle Rückgängigmachen der Transaktion – also der neu geschriebenen Zeilen oberhalb der HWM – ist auch trivial: Der belegte Platz oberhalb der aktuellen HWM wird einfach freigegeben und nicht wie im COMMIT-Fall die HWM entsprechend angepasst – also auch dafür wird keine Undo-Information benötigt. Selbst das Pflegen eventuell bereits existierender Indizes auf der Tabelle kann im APPEND-Modus anders gehandhabt werden – anstatt für jede erzeugte Zeile einzeln die Indizes zu aktualisieren, kann dies auch in einer Art Batch-Operation durchgeführt werden, was meistens effizienter ist, als eine zeilenweise Verarbeitung.
Dieser APPEND-Modus erlaubt es daher auch, die neu erzeugten Blöcke oberhalb der HWM speziell zu behandeln – zum Beispiel in Form von Datenkompression. Bis zur Version 12.2 konnte die Datenbank neu eingefügte Datensätze nur dann mit der "Hybrid Columnar Compression" (HCC – lizenztechnisch nur auf exadata-basierten Umgebungen wie "Exadata on premise", "Exadata Cloud at Customer" oder auch "Autonomous Database" verfügbar) versehen, wenn beim INSERT der APPEND-Modus verwendet wurde. Seit der Version 12.2 geht das mit der HCC-Kompression auch bei Batch/Array-Inserts, die nicht den APPEND-Modus verwenden.
Auf den APPEND-Modus kommen wir später nochmal zurück – hier nur nochmal der Hinweis, dass aufgrund der gerade beschriebenen Vorgehensweise dabei immer nur eine Session gleichzeitig diesen Modus verwenden kann – also ein exklusiver Lock auf die Tabelle dabei entsteht und andere DML-Operationen auf den Abschluss dieser Operation warten müssen. Der Modus ist also nicht geeignet, wenn viele Sessions gleichzeitig die Tabelle verändern können sollen. Im Umkehrschluss bedeutet diese Freiheit beim Ablegen von Daten in einer Heap-Tabelle allerdings auch, dass beim Lesen der Daten von keinerlei Voraussetzungen ausgegangen werden kann: Die gesuchten Daten können eben "irgendwo" in der Tabelle stehen – es gibt keine Annahme oder Regel, wo diese in der Tabelle zu finden sein könnten. Daher gibt es ohne weitere Sekundärstrukturen bei Heap-Tabellen eben nur eine Zugriffsform – den "Full Table Scan", bei dem schlicht alle Zeilen in der Tabelle gelesen werden müssen.
Um bestimmte Zugriffsmuster zu beschleunigen, werden daher in vielen Fällen weitere Hilfsstrukturen über die Heap-Tabelle hinaus benötigt – typischerweise in Form einer Index-Struktur, also einer vorsortierten, redundanten Kopie (üblicherweise) eines Teils der Tabellendaten. Durch die andere physische Organisation der Kopie kann beim Zugriff von bestimmten Voraussetzungen ausgegangen werden, was je nach Zugriffsform bedeutet, dass eben nicht die gesamte Struktur durchsucht werden muss, sondern nur ein mehr oder weniger kleiner Teil, was dann weniger Arbeit beim lesenden Zugriff bedeuten kann. Das Pflegen einer solchen alternativen, regelbehafteten Organisationsform ist allerdings deutlich aufwändiger und komplexer als bei einer Heap-Struktur – die Daten können eben nicht einfach "irgendwo" abgelegt werden, sondern müssen gemäß der Regel logisch gesehen an einer bestimmten Stelle oder in einem bestimmten Bereich gespeichert werden. Hier kann es dann auch zu allerlei Seiteneffekten kommen, zum Beispiel, dass an der Stelle, wo die Daten gemäß der Regeln logisch abgelegt werden müssen, nicht mehr ausreichend Platz zur Verfügung steht. Dies kann eine Kaskade von Datenreorganisationen nach sich führen, die sehr viel Aufwand verursachen und zusätzlich den gleichzeitigen Zugriff mehrerer Sessions beeinflussen und verlangsamen können.
Das gleichzeitige Schreiben von ähnlichen Daten, die also dann gemäß der Vorgaben an gleicher Stelle abgelegt werden müssen, kann bei solchen Strukturen auch zu Verlangsamungen führen, da eine Veränderung der Daten intern für einen kurzen Moment nur bei exklusivem Zugriff möglich ist – und andere Sessions müssen dann eben warten, bis sie den exklusiven Zugriff durchführen können, sollte eine andere Session gerade an gleicher Stelle tätig sein (Oracle instrumentiert das unter anderem als "Buffer Busy Waits"-Warteereignis).
Das heißt im Grunde, dass eine Heap-Tabelle optimal zum effizienten (und auch gleichzeitigen) Schreiben von Daten ist, aber für effiziente Lesezugriffe bei vielen Zugriffsmustern auf Sekundärstrukturen angewiesen ist, die wiederum entsprechende Nachteile beim Schreiben und Verändern von Daten mit sich bringen.
Bei anderen Datenbanken ist die Heap-Organisationsform für Tabellen nicht immer der Standard – bei Microsoft SQL Server zum Beispiel werden die Daten standardmäßig in einem "Clustered Index" gespeichert, wenn ein PRIMARY KEY auf einer Tabelle definiert wird – ein "Clustered Index" bedeutet aber, dass die Tabellendaten selbst in der vorsortierten Index-Struktur abgelegt werden und es keine separate Heap-Struktur gibt.
Alternative Organisationsformen – Vorteile und Nachteile
Daten einer Tabelle in dieser Form zu organisieren – auch bei Oracle ist diese Speicherform möglich in Form einer sogenannten "Index Organized Table" (IOT) – kann dann große Vorteile bieten, wenn ein maßgeblicher Teil der Zugriffe auf die Daten gemäß der Vorsortierung passiert – dann nämlich stehen die gesuchten Daten alle zusammenhängend an der gleichen Stelle der Struktur und es muss nur genau so viel gelesen werden, wie die zusammenhängenden Daten an Platz benötigen. Bei Oracle hat diese Index Organized Table aber eine signifikante Einschränkung – die Vorsortierung ist nur gemäß des Primary Keys der Tabelle möglich, ein alternativer Cluster Key ungleich des Primary Keys ist nicht vorgesehen – Clustered-Index-Objekte in Microsoft SQL Server zum Beispiel sind hier flexibler.
Nicht immer aber wird auf die Daten gemäß dieser Vorsortierung zugegriffen, dann werden für einen effizienten Zugriff eventuell wieder weitere Sekundärobjekte benötigt. Und hier kann es eben zu größeren Nachteilen kommen im Vergleich zur Heap-Tabelle. Denn diese Sekundärobjekte – also meistens Indizes mit anderer Sortierreihenfolge – müssen ja einen Zeiger auf die entsprechende Tabellenzeile beinhalten, um eventuell weitere Spalten der Tabelle für die identifizierte Zeile lesen zu können, die nicht in der Indexstruktur abgebildet sind. Bei Heap-Tabellen reicht hier ein sogenannter "Row Pointer", der die physische Adresse der Zeile in der Tabelle beschreibt – bei Oracle die sogenannte "ROWID". Über diesen Pointer kann die entsprechende Zeile der Tabelle direkt ohne weitere Zugriffe angesteuert werden.
Bei index-basierten Strukturen (Clustered Index, Index Organized Table) reicht dies aber nicht, da die Zeilen in einer solchen Struktur über die Zeit physisch gesehen an unterschiedlichen Stellen gespeichert sein können – eben unter anderem aus dem Grund, dass beim Einfügen neuer Daten eventuell nicht mehr ausreichend Platz an der gefragten Stelle ist und die Daten deswegen umorganisiert werden müssen ("Index Block Split"-Operation). Insofern muss ein solcher Sekundärindex immer das Schlüsselkriterium für die primäre (Tabellen-)Index-Struktur beinhalten und ein Zugriff per Sekundärindex bedeutet für jede Zeile, die aus der Tabelle gelesen werden muss, dass die Index-Struktur der Tabelle auch durchsucht wird, um die Zeile zu lokalisieren. Das hat zwei wesentliche Effekte: Erstens benötigt der Sekundärindex potenziell deutlich mehr Platz pro Eintrag, je nachdem wie das Schlüssel-/Sortierkriterium der Tabelle aussieht (man stelle sich zum Beispiel mehrere VARCHAR-basierte Spalten als Schlüssel vor), zweitens ist der Zugriff auf eine Tabellenzeile per Sekundärindex deutlich aufwändiger, da nicht einfach per "Row Pointer" direkt auf die Zeile zugegriffen werden kann, sondern per Index-Suche.
Verwende ich also eine alternative Speicherform zur Heap-Tabelle und setze diese aber nicht effizient ein, benötige also zum Beispiel im Falle einer indexbasierten Organisationsform noch weitere Sekundärindizes für einen effizienten Zugriff, dann erkaufe ich mir möglicherweise deutliche Nachteile mit dieser Entscheidung gegenüber einer Heap-Tabelle.
Es gibt bei Oracle noch weitere, praktische Gründe, die gegen eine Nutzung von alternativen Speicherformen wie IOTs oder Cluster sprechen können. IOTs bei Oracle können im Index-Segment nur eine begrenzte Breite von Informationen speichern, da es ein internes Limit gibt, wie breit ein Eintrag in einer B*Tree-Indexstruktur sein kann – Spalten, die in einem IOT gespeichert werden sollen, aber bezüglich der Breite nicht innerhalb dieses Limits liegen, benötigen zwingend ein sogenanntes "Overflow"-Segment – dies ist eine zusätzliche Heap-Segment-Struktur, in der diese restlichen Spalten einer Zeile abgespeichert werden. Beim Zugriff auf solche Spalten muss dann also dieses zusätzliche Segment angesprochen werden, was die Effizienz wieder je nach Zugriffsfrequenz und -art deutlich verringern kann. Das Feature kann natürlich auch gezielt zur vertikalen Partitionierung von Daten eingesetzt werden – um Spalten, die nicht so häufig verwendet werden, von den restlichen, häufiger verwendeten Spalten zu separieren, um das IOT-Segment möglichst kompakt zu halten. Dies ist eine Möglichkeit, die es für Heap-Tabellen so nicht gibt – auch wenn man über Objekt-Typen/ Nested Tables so etwas auch bei Heap-Tabellen erreichen kann.
Außerdem gelten für viele Features und Funktionalitäten bei Oracle Einschränkungen, wenn keine Heap-Tabellen zum Einsatz kommen oder viele – gerade neuere – Features nur im Zusammenhang mit Heap-Tabellen zur Verfügung stehen und – wenn überhaupt – nur mit einiger Verzögerung für alternative Speicherformen umgesetzt werden, viele auch gar nicht.
Warum gibt es dann diese alternativen Speicherformen, wenn diese insbesondere bei "unsachgemäßem" Einsatz durchaus mit einigen Nachteilen einhergehen können? Weil sie eben bei bestimmten Zugriffsmustern signifikant effizienter sein können als eine Heap-Tabelle. Warum ist das so? Weil bei der Heap-Tabelle gemäß Definition eben keine direkte Beeinflussung auf die Art und Weise möglich ist, wie die Daten in der Tabelle organisiert sind. Es ergibt sich bei einer Heap-Tabelle zwar häufig eine "natürliche" Organisation der Daten, in dem Sinne, dass Daten, die zu einem ähnlichen Zeitpunkt erzeugt worden sind, meistens auch physisch nahe beisammen oder zusammenhängend in der Heap-Tabelle gespeichert sind, also in den gleichen Blöcken (oder auch Seiten, je nach Datenbanksystem). Das muss aber nicht unbedingt der Fall sein, je nachdem, wo eben laut Freiplatzverwaltung gerade Platz für die neuen Daten ist. Das bedeutet dann aber eben auch, dass Zugriffsmuster, die auf Daten zugreifen, die in der Heap-Tabelle nicht zusammenhängend gespeichert sind, auf deutlich mehr Blöcke/Seiten zugreifen müssen, als im optimalen Fall notwendig. Im schlechtesten Fall muss für jede zugegriffene Zeile ein jeweils anderer Block gelesen werden, was vor allem deutlich mehr physisches I/O verursachen und die Effizienz des Daten-Caches der Datenbank signifikant verschlechtern kann. Dabei können Szenarien entstehen, bei denen im Vergleich zum Full Table Scan ein Vielfaches an I/O notwendig ist. Bei diesem lese ich jeden Block genau einmal, bei einem solchen "Worst Case"-Zugriffsmuster jedoch so viele Blöcke wie Zeilen, auf die zugegriffen wird. Hat eine Tabelle also zum Beispiel im Durchschnitt 100 Zeilen pro Block und ich lese die gesamte Tabelle mittels eines solchen Zugriffsmusters, greife ich auf jeden Block 100-mal zu – erzeuge also um den Faktor 100 mehr I/O – meistens dann auch tatsächlich 100-mal mehr physisches I/O, wenn die Tabelle deutlich größer als der Daten-Cache der Datenbank ist. Dieser Effekt entsteht dadurch, dass die gelesenen Blöcke sich gegenseitig aus dem Cache verdrängen und dieser sich beim nächsten Zugriff auf den gleichen Block schon nicht mehr im Cache befindet und wieder einen physischen Lesezugriff benötigt, um in den Cache eingelesen zu werden.
Ein praktisches Beispiel
Anhand eines einfachen Beispiels soll dieser Effekt des unterschiedlichen Clusterings der Daten deutlich gemacht werden. Nehmen wir an, es sollen Aktienkurse in einer Tabelle abgespeichert werden – der Einfachheit halber ein Stand pro Tag. Verwende ich eine Heap-Tabelle dafür und ignoriere für den Augenblick weitere Effekte, die durch das Löschen/Verändern von Daten entstehen können, werden die Daten eines jeden Tages mit großer Wahrscheinlichkeit physisch zusammenhängend in der Tabelle abgelegt werden, da sie zur gleichen Zeit der Tabelle per Insert hinzugefügt werden. Das könnte man mittels folgender Tabellendefinition und PL/SQL-Blocks in Oracle simulieren:
create table stock_history (
ticker_code varchar2(32),
trade_date date,
price_close number(15,2),
trade_volume number(10),
price_high number(15,2),
price_low number(15,2),
constraint stock_history_pk primary key (ticker_code, trade_date)
)
;
begin
for i in 1..1000 loop
insert into stock_history (
ticker_code,
trade_date,
price_close,
trade_volume,
price_high,
price_low)
select
ticker_code,
trade_date,
price_close,
trade_volume,
price_high,
price_low
from (
select
ticker_code
, date '2000-01-01' + i - 1 as trade_date
, i * company_code as price_close
, i * company_code as trade_volume
, i * company_code as price_high
, i * company_code as price_low
from (
select /*+ cardinality(1000) */
'COMPANY_' || to_char(level - 1, 'FM000') as ticker_code
, level - 1 as company_code
from
dual
connect by level <= 1000
) companys
)
;
end loop;
end;
/
commit;
exec dbms_stats.gather_table_stats(null, 'STOCK_HISTORY');Damit erstelle ich genau eine Million Zeilen – eintausend Einträge für eintausend verschiedene Aktien jeweils für eintausend Tage. Schaue ich mir die Daten in der Tabelle an, sehe ich, dass die Einträge pro TRADE_DATE zusammenhängend in der Tabelle gespeichert sind – es kommen also erst alle TICKER_CODEs für ein TRADE_DATE, und danach folgen die gleichen TICKER_CODEs nochmal für das folgende TRADE_DATE:
select * from stock_history;
TICKER_CODE TRADE_DATE PRICE_CLOSE TRADE_VOLUME PRICE_HIGH PRICE_LOW
-------------------------------- ----------------- ----------- ------------ ---------- ----------
COMPANY_000 20000101 00:00:00 0 0 0 0
COMPANY_001 20000101 00:00:00 1 1 1 1
COMPANY_002 20000101 00:00:00 2 2 2 2
COMPANY_003 20000101 00:00:00 3 3 3 3
COMPANY_004 20000101 00:00:00 4 4 4 4
COMPANY_005 20000101 00:00:00 5 5 5 5
COMPANY_006 20000101 00:00:00 6 6 6 6
COMPANY_007 20000101 00:00:00 7 7 7 7
COMPANY_008 20000101 00:00:00 8 8 8 8
COMPANY_009 20000101 00:00:00 9 9 9 9
COMPANY_010 20000101 00:00:00 10 10 10 10
.
.
.
COMPANY_990 20000101 00:00:00 990 990 990 990
COMPANY_991 20000101 00:00:00 991 991 991 991
COMPANY_992 20000101 00:00:00 992 992 992 992
COMPANY_993 20000101 00:00:00 993 993 993 993
COMPANY_994 20000101 00:00:00 994 994 994 994
COMPANY_995 20000101 00:00:00 995 995 995 995
COMPANY_996 20000101 00:00:00 996 996 996 996
COMPANY_997 20000101 00:00:00 997 997 997 997
COMPANY_998 20000101 00:00:00 998 998 998 998
COMPANY_999 20000101 00:00:00 999 999 999 999
COMPANY_000 20000102 00:00:00 0 0 0 0
COMPANY_001 20000102 00:00:00 2 2 2 2
COMPANY_002 20000102 00:00:00 4 4 4 4
COMPANY_003 20000102 00:00:00 6 6 6 6
COMPANY_004 20000102 00:00:00 8 8 8 8
COMPANY_005 20000102 00:00:00 10 10 10 10
COMPANY_006 20000102 00:00:00 12 12 12 12
COMPANY_007 20000102 00:00:00 14 14 14 14
COMPANY_008 20000102 00:00:00 16 16 16 16
COMPANY_009 20000102 00:00:00 18 18 18 18
COMPANY_010 20000102 00:00:00 20 20 20 20
.
.
.
Wenn ich jetzt eine Abfrage auf dieser Tabelle ausführe, die die Kurse einer bestimmten Aktie für zwei Jahre ausgeben soll, bekomme ich folgendes Bild:
set autotrace traceonly
select * from stock_history where ticker_code = 'COMPANY_100' and trade_date between date '2000-01-01' and date '2001-12-31' order by trade_date;
731 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2261377770
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 732 | 29280 | 740 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| STOCK_HISTORY | 732 | 29280 | 740 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | STOCK_HISTORY_PK | 732 | | 8 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TICKER_CODE"='COMPANY_100' AND "TRADE_DATE">=TO_DATE(' 2000-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRADE_DATE"<=TO_DATE(' 2001-12-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
741 consistent gets
0 physical reads
0 redo size
30304 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
731 rows processed
Es werden also 731 Zeilen per Primär-Index-Zugriff (TICKER_CODE, TRADE_DATE) selektiert. Das sieht auf den ersten Blick wie ein effizienter Zugriff aus – nur wenn wir genauer hinschauen, bemerken wir, dass Oracle ausgibt, dass für das Selektieren der 731 Zeilen 741 Blöcke besucht werden mussten ("consistent gets" – logisches I/O in diesem Fall, also kein physisches I/O, um die Blöcke in den Cache zu lesen). Im Grunde also wurde für jede selektierte Zeile per Index auf einen anderen Block der Tabelle zugegriffen. Warum ist das so?
Wenn wir uns die Ausgabe von oben nochmal genau anschauen, dann wird klar, dass jeder der gesuchten Einträge für eine bestimmte Aktie in der Tabelle jeweils eintausend Zeilen auseinander gespeichert ist, da ja immer erst mal alle eintausend Einträge/Aktienkurse für ein bestimmtes Datum hintereinander folgen und erst danach die nächsten eintausend für den nächsten Tag. Dadurch muss diese Abfrage in der Tabelle immer per Index eintausend Zeilen weiter springen, was dazu führt, dass mit großer Wahrscheinlichkeit jede Zeile in einem anderen Tabellenblock steht, auch wenn ein Block in Oracle heutzutage standardmäßig 8 KB groß ist und je nach Zeilenbreite hunderte von Zeilen aufnehmen kann.
Wenn es sich nun bei dieser Art von Abfrage um eine für meine Applikation kritische handeln würde, die sehr häufig auf einer entsprechend großen Datenmenge ausgeführt würde, wäre es natürlich toll, wenn ich die Effizienz erhöhen könnte. Denn so, wie es derzeit aussieht, lese ich für jede Zeile 8 KB an Daten in den Cache, und damit eine große Menge an Zeilen, die mich in diesem Moment überhaupt nicht interessieren. Das ist sehr ineffizient, denn es erhöht die Wahrscheinlichkeit, dass die vielen verschiedenen Blöcke nicht im Cache sind und vom Storage gelesen werden müssen, was um Faktoren langsamer ist, als die Daten direkt aus dem Cache/Arbeitsspeicher zu lesen. Außerdem "verschmutze" ich den Cache mit vielen unnützen Daten für diese Abfrage und mindere damit indirekt auch die Chancen für andere Abfragen, dass sie Daten im Cache halten können.
Um also diese Art der Abfrage effizienter gestalten zu können, müsste ich die Daten in der Tabelle nicht nach TRADE_DATE hintereinander abspeichern, sondern zuerst alle Einträge für eine Aktie hintereinander speichern – dann würden die Zeilen, die ich für eine bestimmte Aktie suche, alle zusammenhängend in wenigen Blöcken der Tabelle gespeichert stehen und ich müsste nicht für jede Zeile in einen anderen Block springen.
Um das zu erreichen, gibt es in relationalen Datenbanken unterschiedliche Möglichkeiten. Ich könnte zum Beispiel versuchen, einen Index zu erzeugen, der alle benötigten Spalten/Ausdrücke im Indexsegment abspeichert. Dann muss die Datenbank gar nicht mehr auf das Tabellensegment zugreifen (Covering Index). Das macht aber den Index sehr breit, speichert viele Daten redundant ab und ist nicht immer sinnvoll umsetzbar – wenn auch auf jeden Fall je nach Szenario eine mögliche Option.
Optimierung durch Einsatz einer "Index Organized Table" (IOT)
Alternativ dazu könnte man die Tabelle direkt als Index-Struktur ablegen und in anderen Datenbanksystemen gibt es dazu einen "Clustered Index". In Oracle heißt das äquivalente Konstrukt "Index Organized Table" (IOT). Das hat aber einige Eigenschaften und Nachteile, die man kennen sollte, wenn man es zum Einsatz bringt, s. Beschreibung oben. Für das konkrete Szenario STOCK_HISTORY und die beschriebene Abfrage oben wäre es potentiell eine sehr effiziente Lösung, abhängig davon, was sonst noch mit der Tabelle und den Daten gemacht wird.
Wenn man das machen wollte, würde die Tabelle wie folgt erzeugt werden:
create table stock_history (
ticker_code varchar2(32),
trade_date date,
price_close number(15,2),
trade_volume number(10),
price_high number(15,2),
price_low number(15,2),
constraint stock_history_pk primary key (ticker_code, trade_date)
)
organization index
compress 1
;
Was hat es mit der Klausel organization index auf sich? Die Tabelle würde nun also nicht als Heap-Tabelle erzeugt werden, stattdessen würde eine Index-Struktur (intern wie ein B*Tree-Index organisiert) angelegt werden, in der die Daten nach den Primärschlüssel-Kriterien sortiert abgelegt werden. IOTs benötigen also in Oracle immer eine Primary Key Definition, ansonsten können sie nicht erzeugt werden. Durch die Definition TICKER_CODE, TRADE_DATE würden die Daten also primär nach dem Namen der Aktie sortiert werden, und innerhalb einer Aktie nach TRADE_DATE. Ich hätte damit die Daten also perfekt für die Abfrage nach den unterschiedlichen Aktienkursen für eine einzelne Aktie abgelegt, was sich auch in der Ausgabe der gleichen Abfrage auf einer entsprechend erzeugten und befüllten IOT widerspiegelt:
set autotrace traceonly
select * from stock_history where ticker_code = 'COMPANY_100' and trade_date between date '2000-01-01' and date '2001-12-31' order by trade_date;
731 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2271383356
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 732 | 29280 | 6 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| STOCK_HISTORY_PK | 732 | 29280 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TICKER_CODE"='COMPANY_100' AND "TRADE_DATE">=TO_DATE('
2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRADE_DATE"<=TO_DATE('
2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
29376 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
731 rows processed
Schaut man sich die Ausgabe genauer an, fallen mehrere Punkte auf: Erstens gibt es gar keinen Tabellenzugriff mehr – kein TABLE ACCESS BY ROWID im Ausführungsplan wie in der vorherigen Ausgabe – denn es gibt eben nur noch die Index-Struktur der IOT, kein Heap-Tabellensegment mehr. Zweitens hat die gleiche Abfrage auf den gleichen Daten jetzt nur noch sieben Blöcke lesen müssen, um die 731 Zeilen zu selektieren, da die gesuchten Daten nun alle zusammenhängend in der Index-Struktur an der gleichen Stelle stehen – eben für eine Aktie alle TRADE_DATEs hintereinander gemäß der Primärschlüssel-Definition.
Das ist um Faktor 100 effizienter als vorher – führt man also diese Art der Abfrage häufig aus, spart man extrem viel Arbeit in der Datenbank ein. Außerdem hat man jetzt für die gleiche Datenmenge maximal sieben Blöcke in den Cache einlesen müssen, anstatt wie vorher über 740. Die Wahrscheinlichkeit, dass diese im Cache gehalten werden können, ist also viel größer – und für andere Abfragen/Blöcke ist auch noch deutlich mehr Platz im Cache verfügbar, selbst wenn ich diese Art der Abfrage häufig ausführe.
Index-Kompression
Ich habe bei der Definition der IOT auch noch eine weitere Klausel angegeben: COMPRESS 1. Diese ist optional, kann aber auch sehr nützlich sein, denn Oracle kann eine B*Tree-Index-Struktur ohne signifikante Nachteile komprimieren. Das funktioniert deshalb so effizient, da Oracle einfach den führenden Teil des Index-Ausdrucks innerhalb eines Blocks deduplizieren kann, wenn sich dieser wiederholt. Da sich der TICKER_CODE für jedes TRADE_DATE wiederholt, also in dem konkreten Beispiel eintausend Mal der gleiche TICKER_CODE mit unterschiedlichen TRADE_DATEs abgelegt wird, spart es Platz im Index, wenn der TICKER_CODE stattdessen nur einmal abgespeichert wird und in den eigentlichen Daten nur ein Verweis auf den Eintrag. Diese Art der Komprimierung ist also intern von Oracle sehr einfach umzusetzen, benötigt nicht viel zusätzlich CPU-Zeit, spart potentiell aber signifikant Platz im Index und ermöglicht so also deutlich mehr Einträge pro Index-Block abzuspeichern, wenn sich die führenden Werte im Index wiederholen – macht aber eben auch nur Sinn, wenn das der Fall ist. Sollten sich die Einträge nicht oder nur wenig wiederholen, würde diese Art der Kompression tatsächlich mehr Platz als ohne Kompression benötigen. Insofern kann man es leider nicht in jedem Fall anwenden. Leider kommen IOTs in Oracle mit vielen Einschränkungen und Nachteilen. Je nachdem, wie auf die Daten zugegriffen werden soll, kann dies nicht immer sinnvoll als Primärschlüssel abgebildet werden. Wenn noch mittels anderer Kriterien per Index auf die Daten zugegriffen werden soll, sind weitere Indizes auf einer IOT unter Umständen deutlich größer und ineffizienter als auf Heap-Tabellen. Falls die Tabelle viele oder breite Spalten hat, können nicht alle Spalten im Index-Segment abgelegt werden, da Oracle hier interne Beschränkungen hat. Außerdem unterstützen IOTs einige Features nicht, die für Heap-Tabellen zur Verfügung stehen.
Lesen Sie den zweiten Teil des Artikels hier...
Neuen Kommentar schreiben