Attribute Clustering, unterschätztes Oracle-Feature (II)
Dies ist der zweite Teil des Artikels. Lesen Sie den ersten Teil hier.
Optimierung mittels Attribute Clusterings
Seit Oracle 12c hat Oracle aber auch noch ein weiteres Feature diesbezüglich im Angebot: Attribute Clustering. Es bietet die Möglichkeit, die physische Organisation der Daten einer Heap-Tabelle eben doch zu beeinflussen. Denn Attribute Clustering ermöglicht es, für die eigentlich "organisationslose" Heap-Tabelle doch eine Vorgabe zu machen, wie die Daten physisch organisiert sein sollen.
Wie soll das gehen, denn definitionsgemäß heißt doch eine Heap-Tabelle genau deswegen so, weil es keine Vorgaben gibt, wo Daten darin abzulegen sind? Das Attribute Clustering bei Oracle wird daher nur bei bestimmten Operationen auf der Heap-Tabelle berücksichtigt – immer dann nämlich, wenn direkt neue Blöcke mit Daten geschrieben werden. Das ist zum Beispiel bei dem erwähnten APPEND-Modus des Inserts der Fall, bei einer CREATE TABLE AS SELECT-Operation, oder auch bei einem ALTER TABLE MOVE, wenn die Tabelle reorganisiert und dabei komplett neu geschrieben wird.
Währenddessen ignoriert die Datenbank das Attribute Clustering bei konventionellem DML – wird also ein normales Insert in die Heap-Tabelle gemacht, gelten auch die bereits beschriebenen, normalen Regeln – dort wo Platz ist, wird die Zeile hingeschrieben, ungeachtet irgendwelcher Vorgaben mittels Attribute Clustering, wie die Daten organisiert sein sollen.
Um also das Attribute Clustering effektiv auf eine Heap-Tabelle anzuwenden, müssen die Daten per APPEND-Modus eingefügt oder per CREATE TABLE AS SELECT-Operation erzeugt werden – dies ist häufig in Data-Warehouse-Umgebungen der Fall, wenn Daten im Rahmen von ETL-Transformationen in Batches oder per Exchange-Partition-Operationen verarbeitet werden.
Ist dies nicht der Fall, müssen neu erzeugte Daten regelmäßig per ALTER TABLE MOVE reorganisiert werden – auch dies ist in neueren Versionen von Oracle in vielen Fällen inzwischen per ONLINE-Operation möglich, also auch während DML auf der Tabelle aktiv ist. Idealerweise kann man das mit einer geeigneten Partitionierung (separate Lizenz bei Oracle notwendig) verknüpfen, so dass alte Daten, die bereits reorganisiert wurden, nicht mehr berücksichtigt werden müssen.
Eigenschaften und Auswirkungen des neuen Attribute-Clustering-Features
Attribute Clustering bedeutet, dass ich der Datenbank per Metadatendefinition mitteile, dass die Daten in einer Heap-Tabelle gemäß eines oder mehrerer Kriterien organisiert sein sollen. Daten mit gleicher Ausprägung dieser Kriterien werden also physisch zusammenhängend in der Tabelle abgelegt, was beim lesenden Zugriff bedeutet, dass nur auf so viele Blöcke zugegriffen werden muss, wie diese zusammenhängend gespeicherten Daten allokieren. Beim Einsatz von Exadata Storage Indizes im Rahmen der sogenannten "Smart Scans" (Full Table Scan wird an die Storage Cells von Exadata ausgelagert), InMemory-Column-Store-basierten Full Table Scans oder Zonemaps (leider auch nur in Exadata-Umgebungen aus lizenztechnischen Gründen verfügbar) gilt dies sogar für Full Table Scans, also können auch diese durch Attribute Clustering signifikant beschleunigt/optimiert werden, da nur die relevanten Bereiche der Tabelle verarbeitet werden, und der Rest wird "ignoriert"/übersprungen – ansonsten eben insbesondere bei indexbasierten Zugriffsmustern.
Wie würde also nun in dem konkreten Beispiel die Verwendung von Attribute Clustering aussehen? Das CREATE TABLE-Kommando zum Erzeugen einer entsprechenden Heap-Tabelle mit Attribute Clustering könnte so aussehen:
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)
)
CLUSTERING BY LINEAR ORDER (ticker_code, trade_date)
;
Anstelle der ORGANIZATION INDEX-Option ist nun also die CLUSTERING BY-Angabe verwendet worden, die der Datenbank mitteilt, dass die Daten in der Heap-Tabelle gemäß der angegebenen Kriterien zusammenhängend physisch abgelegt werden sollen. Dies kann auch nachträglich für bereits existierende Tabellen mittels ALTER TABLE definiert werden, oder auch nachträglich wieder entfernt und durch eine andere Clustering-Definition ersetzt werden.
LINEAR ORDER ist der Standardfall und bedeutet, dass bei Angabe von mehreren Kriterien – hier TICKER_CODE und TRADE_DATE – die Daten zuerst primär nach TICKER_CODE zusammenhängend gespeichert werden und falls es mehrere Zeilen mit dem gleichen TICKER_CODE gibt, dann Daten, welche gemäß des TRADE_DATEs zusammenhängend sind. Das heißt, dass diese Art des Clusterings nur für Abfragen von Vorteil ist, die primär nach dem TICKER_CODE abfragen, und eventuell noch zusätzlich nach dem TRADE_DATE, aber nicht für Abfragen, die nur nach dem TRADE_DATE abfragen, denn das primäre Kriterium ist nun mal der TICKER_CODE. Die Daten sind eben nicht zusammenhängend nach TRADE_DATE abgelegt, sondern nur bei gleichem TICKER_CODE innerhalb dieser Menge nach TRADE_DATE zusammenhängend.
Diese Art des Clusterings von Heap-Tabellen könnte ich auch manuell umsetzen – also ohne die CLUSTERING BY-Definition – wenn ich die Tabelle mittels CREATE TABLE AS SELECT … ORDER BY oder INSERT /*+ APPEND */ INTO TABLE SELECT … ORDER BY befüllen würde. Ich könnte also auch diesen Effekt in der “Standard-Edition“ von Oracle ohne das Attribute-Clustering-Feature erreichen, wenn ich den Aufwand dafür in Kauf nehme.
Wie bereits beschrieben, wird diese Angabe aber nur bei speziellen Operationen auf der Heap-Tabelle von Oracle berücksichtigt. Verwende ich den gleichen PL/SQL-Block wie bisher zum Befüllen der Tabelle, um das Hinzufügen von täglichen Kursständen zu simulieren, kommen dort konventionelle INSERT-Befehle zum Einsatz, und nicht die Spezialform INSERT APPEND – so dass das Clustering eben nicht angewendet werden wird und de facto erst mal keine Auswirkung hat. Erst wenn ich zum Beispiel ein ALTER TABLE MOVE auf die befüllte Tabelle durchführe, wird dabei die Clustering-Definition berücksichtigt und die Daten bei der Reorganisation der Tabelle entsprechend abgelegt:
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_000 20000102 00:00:00 0 0 0 0
COMPANY_000 20000103 00:00:00 0 0 0 0
COMPANY_000 20000104 00:00:00 0 0 0 0
COMPANY_000 20000105 00:00:00 0 0 0 0
COMPANY_000 20000106 00:00:00 0 0 0 0
COMPANY_000 20000107 00:00:00 0 0 0 0
COMPANY_000 20000108 00:00:00 0 0 0 0
COMPANY_000 20000109 00:00:00 0 0 0 0
COMPANY_000 20000110 00:00:00 0 0 0 0
.
.
.
COMPANY_000 20020917 00:00:00 0 0 0 0
COMPANY_000 20020918 00:00:00 0 0 0 0
COMPANY_000 20020919 00:00:00 0 0 0 0
COMPANY_000 20020920 00:00:00 0 0 0 0
COMPANY_000 20020921 00:00:00 0 0 0 0
COMPANY_000 20020922 00:00:00 0 0 0 0
COMPANY_000 20020923 00:00:00 0 0 0 0
COMPANY_000 20020924 00:00:00 0 0 0 0
COMPANY_000 20020925 00:00:00 0 0 0 0
COMPANY_000 20020926 00:00:00 0 0 0 0
COMPANY_001 20000101 00:00:00 1 1 1 1
COMPANY_001 20000102 00:00:00 2 2 2 2
COMPANY_001 20000103 00:00:00 3 3 3 3
COMPANY_001 20000104 00:00:00 4 4 4 4
COMPANY_001 20000105 00:00:00 5 5 5 5
COMPANY_001 20000106 00:00:00 6 6 6 6
COMPANY_001 20000107 00:00:00 7 7 7 7
COMPANY_001 20000108 00:00:00 8 8 8 8
COMPANY_001 20000109 00:00:00 9 9 9 9
COMPANY_001 20000110 00:00:00 10 10 10 10
.
.
.
Wie man jetzt sehen kann, sind die Daten nach dem Befüllen per PL/SQL-Block und Durchführen von ALTER TABLE MOVE tatsächlich physisch anders angeordnet – zuerst kommen alle TRADE_DATEs zum gleichen TICKER_CODE, dann folgt der nächste TICKER_CODE und dafür wieder alle TRADE_DATEs und so weiter.
Führe ich nun also wieder die gleiche Abfrage wie bisher aus, die die Historie von zwei Jahren für einen bestimmten TICKER_CODE selektiert, sind die gesuchten Daten in der Tabelle zusammenhängend abgelegt und nicht jeweils 1000 Zeilen voneinander entfernt. Entsprechend wird der Zugriff per gleichem Index immer wieder in die gleichen Tabellenblöcke springen, in denen die gesuchten Zeilen zusammenhängend gespeichert sind. Entsprechend ist weniger logisches I/O notwendig – und als Konsequenz daraus bei größeren Tabellen auch weniger physisches I/O, um die 731 Zeilen zu selektieren:
SQL> 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 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| STOCK_HISTORY | 732 | 29280 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | STOCK_HISTORY_PK | 732 | | 6 (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
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
132 redo size
30304 bytes sent via SQL*Net to client
382 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
731 rows processed
Ähnlich wie beim Index Organized Table habe ich also diese Art der Abfrage um viele Faktoren effizienter gemacht – es gibt zwar immer noch einen Tabellenzugriff im Ausführungsplan (TABLE ACCESS BY INDEX ROWID), aber es wird nur auf 8 Blöcke der Tabelle dafür zugegriffen, in denen die gesuchten Zeilen zusammenhängend abgelegt sind, anstatt für jede gesuchte Zeile in einen anderen Block der Tabelle zu springen, so dass in Summe nur 16 "logische" I/Os benötigt werden, um die Abfrage auszuführen.
Wichtig ist dabei auch zu verstehen, dass man sich bei dieser grundlegenden Variante für ein primäres Clustering-Attribut entscheiden muss. Ein Zugriff per TRADE_DATE wäre bei einem primären Clustering nach TICKER_CODE eben nicht mehr so effizient wie es das natürliche Clustering der Tabelle eigentlich ermöglichen würde. Es kommt also darauf an, welche Zugriffsmuster am wichtigsten, häufigsten oder kritischsten sind.
Weitere Features von Attribute Clustering – Interleaved Ordering
Das Attribute Clustering von Oracle bietet über die gerade beschriebene, grundlegende Variante hinaus noch weitere Möglichkeiten. Der Standardfall, der auch mittels Clustered Index, Index Organized Tables (IOTs), Oracle Cluster-Strukturen / ORDER BY-Klausel abgebildet werden kann, sieht, wie gerade erklärt, ein primäres Kriterium (eine Spalte der Tabelle) vor, nachdem die Daten organisiert werden. Das heißt, beim Design muss man sich für ein primäres Kriterium entscheiden. Nur Zugriffe über dieses primäre Kriterium werden optimal durch das zusammenhängende Ablegen der Daten gemäß dieses Kriteriums unterstützt. Bei Oracle IOTs kann dies nur der führende Teil oder der gesamte Primärschlüssel sein.
Das Attribute Clustering für Heap-Tabellen unterstützt aber zum Beispiel auch eine sogenannte Interleaved Order. Hier können – innerhalb bestimmter Grenzen – sogar mehrere voneinander unabhängige Kriterien angegeben werden, nach denen die Daten organisiert werden sollen. Möglich macht das Oracle intern mittels eines sogenannten "Z-Ordering"-Algorithmus. Haben diese unterschiedlichen Kriterien nicht zu viele verschiedene Ausprägungen, funktioniert das recht gut. Der Zugriff pro Kriterium ist dann unter Umständen nicht ganz so optimal wie bei der "Linear Order", bei der es nur ein primäres Kriterium gibt, dafür kann ich den Zugriff auf die Tabelle aber eben für mehrere, voneinander unabhängige Zugriffswege optimieren. Diese Möglichkeit gibt es bei den anderen, alternativen Speicherformen so nicht.
Das funktioniert bei dem bisherigen Beispiel nicht – dafür gibt es zu viele Ausprägungen sowohl von TICKER_CODE als auch TRADE_DATE (1000 mal 1000) in der Tabelle STOCK_HISTORY bzw. die Kombination aus TICKER_CODE und TRADE_DATE ist eindeutig. Man kann also nicht sowohl für den Zugriff per TICKER_CODE als auch per TRADE_DATE unabhängig voneinander optimieren. Das ist mit Attribute Clustering nur möglich, wenn die Anzahl der Kombinationen der Werte der unterschiedlichen Attribute deutlich unter der Anzahl der Zeilen der Tabelle liegt.
Daher hier ein anderes Beispiel, bei dem dies funktionieren kann. Hier haben wir eine Faktentabelle sowie zwei Dimensionstabellen, also ein klassisches Star-Schema:
REM Create the SALES_SOURCE table
REM This will provide us with a consistent dataset
REM for any fact tables we choose to create later on
--
CREATE TABLE sales_source
(
order_id NUMBER(20) NOT NULL ,
order_item_number NUMBER(3) NOT NULL ,
sale_date DATE NOT NULL ,
delivered DATE ,
sale_agent VARCHAR2(100) NOT NULL ,
product_id NUMBER(10) NOT NULL ,
amount NUMBER(10,2) NOT NULL ,
quantity NUMBER(5) NOT NULL ,
location_id NUMBER(20) NOT NULL ,
warehouse VARCHAR2(100) NOT NULL
)
/
REM Create the LOCATIONS table
CREATE TABLE locations
(
location_id NUMBER(20) ,
state VARCHAR2(100) NOT NULL ,
county VARCHAR2(100) NOT NULL ,
description VARCHAR2(1000) NOT NULL ,
PRIMARY KEY (location_id)
)
/
REM Drop the PRODUCTS table
DROP TABLE products
/
REM Create the PRODUCTS table
CREATE TABLE products
(
product_id NUMBER(20) ,
product_name VARCHAR2(20) ,
product_description VARCHAR2(100) ,
PRIMARY KEY(product_id)
)
/
In die Dimensionen werden entsprechende Daten eingefügt:
insert into products values (1,'DECKING','Decking Description');
insert into products values (2,'GARDENLIGHTING','Gardenlighting Description');
insert into products values (3,'GNOME','Gnome Description');
insert into products values (4,'LAMP','Lamp Description');
.
.
.
insert into locations values (1,'Alabama','Autauga County','Alabama Autauga County Description');
insert into locations values (2,'Alabama','Baldwin County','Alabama Baldwin County Description');
insert into locations values (3,'Alabama','Barbour County','Alabama Barbour County Description');
insert into locations values (4,'Alabama','Bibb County','Alabama Bibb County Description');
insert into locations values (5,'Alabama','Blount County','Alabama Blount County Description');
insert into locations values (6,'Alabama','Bullock County','Alabama Bullock County Description');
.
.
.
commit;
--
REM Gather statistics for our dimension tables
--
EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'locations');
EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'products');
Dabei gibt es nur 28 Produkte und etwas mehr als 3000 Lokationen, ausmultipliziert also ca. 90.000 Kombinationen. Die Daten für die Faktentabelle werden wieder mittels PL/SQL-Prozedur erzeugt. Dabei werden Daten für die Jahre 2000 und 2009 exemplarisch generiert. Pro Jahr entstehen ca. eine Million Zeilen, so dass die Faktentabelle für diese zwei Jahre dann ca. zwei Millionen Zeilen enthält:
CREATE sequence nseq CACHE 10000
/
--
REM Utility procedure to fill SALES_SOURCE table
--
CREATE OR REPLACE
PROCEDURE filltab(
p_start_date DATE,
p_number_of_rows NUMBER,
p_number_of_days NUMBER)
AS
TYPE sa_tab_type
IS
TABLE OF sales_source.sale_agent%TYPE INDEX BY BINARY_INTEGER;
sa_tab sa_tab_type;
TYPE wh_type
IS
TABLE OF sales_source.warehouse%TYPE INDEX BY BINARY_INTEGER;
wh_tab wh_type;
sale_date DATE;
num_order_items NUMBER(2);
sa sales_source.sale_agent%TYPE;
product sales_source.product_id%TYPE;
location sales_source.location_id%TYPE;
wh sales_source.warehouse%TYPE;
order_id sales_source.order_id%TYPE;
num_products NUMBER(5);
num_locations NUMBER(5);
max_order_items NUMBER(3) := 20;
num_inserted NUMBER(10) := 0;
loop_count NUMBER(10) := 0;
counter NUMBER(10);
deliv_days NUMBER(3);
BEGIN
sa_tab(1) := 'MARK';
sa_tab(2) := 'CLARE';
sa_tab(3) := 'ANDREW';
sa_tab(4) := 'LUCY';
sa_tab(5) := 'JENNY';
sa_tab(6) := 'JOHN';
sa_tab(7) := 'BRIAN';
sa_tab(8) := 'JANE';
sa_tab(9) := 'ED';
sa_tab(10) := 'SIMON';
sa_tab(11) := 'SALLY';
wh_tab(1) := 'ALBUQUERQUE';
wh_tab(2) := 'WINSTON SALEM';
wh_tab(3) := 'NEWPORT';
wh_tab(4) := 'BIRMINGHAM';
wh_tab(5) := 'OCOEE';
wh_tab(6) := 'PRINCETON';
order_id := nseq.nextval;
sale_date := p_start_date;
SELECT COUNT(*) INTO num_products FROM products;
SELECT COUNT(*) INTO num_locations FROM locations;
LOOP
num_order_items:= dbms_random.value(1,max_order_items+1);
order_id := nseq.nextval;
sale_date := p_start_date + dbms_random.value(0,floor(p_number_of_days+1));
wh := wh_tab(floor(dbms_random.value(1,7)));
sa := sa_tab(floor(dbms_random.value(1,12)));
deliv_days := dbms_random.value(2,30);
INSERT INTO sales_source
SELECT order_id ,
rownum ,
sale_date ,
sale_date + deliv_days ,
sa ,
dbms_random.value(1,floor(num_products)) ,
dbms_random.value(1,2000) ,
dbms_random.value(1,3) ,
dbms_random.value(1,floor(num_locations)) ,
wh
FROM dual
CONNECT BY rownum <= num_order_items;
num_inserted := num_inserted + num_order_items;
loop_count := loop_count + 1;
IF mod(loop_count,1000) = 0 THEN
COMMIT;
END IF;
EXIT WHEN num_inserted >= p_number_of_rows;
END LOOP;
COMMIT;
END;
/
show errors
--
REM Fill the SALES_SOURCE table with data for 2000 and 2009
REM This may take several minutes...
--
EXECUTE filltab(to_date('01-JAN-2000','DD-MON-YYYY'),1452090,364);
EXECUTE filltab(to_date('01-JAN-2009','DD-MON-YYYY'),500000,364);
--
REM Gather table statistics...
--
EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'sales_source')
Zur besseren Vergleichsmöglichkeit erzeugen wir zwei Kopien der eigentlichen Faktentabelle aus den Daten in der gerade befüllten Tabelle SALES_SOURCE, eine ohne Attribute Clustering (SALES), die andere mit (SALES_AC):
REM Create the SALES fact table
REM This table will not have attribute clustering
REM or zone maps. We will use it to compare with
REM an attribute clustered table.
--
CREATE TABLE sales
AS
SELECT * FROM sales_source
WHERE 1 = -1
/
--
REM Create a SALES_AC fact table
REM The data will be the same as SALES
REM but it will be used to demonstrate
REM attribute clustering
REM in comparison to the standard SALES table.
--
CREATE TABLE sales_ac
AS
SELECT * FROM sales_source
WHERE 1 = -1
/
--
REM Here we enable interleaved ordered attribute clustering
--
ALTER TABLE sales_ac
ADD CLUSTERING BY INTERLEAVED ORDER (location_id, product_id)
WITHOUT MATERIALIZED ZONEMAP
/
set timing on
--
REM Insert data into standard table
--
INSERT /*+ APPEND */ INTO sales SELECT * FROM sales_source
/
--
REM Observe that insert plan is a simple insert
--
SELECT * FROM TABLE(dbms_xplan.display_cursor)
/
COMMIT
/
--
REM Insert data into attribute clustered table.
REM We must use a direct path operation to make
REM use of attribute clustering.
REM In real systems we will probably insert in
REM multiple batches: each batch of inserts will be
REM ordered appropriately. Later on,
REM if we want to re-order all rows into
REM tightly grouped zones we can, for example, use
REM partitioning and MOVE PARTITION to do this.
REM
REM Increased elapsed time is likely due
REM to the sort that is transparently performed to cluster
REM the data as it is inserted into the SALES_AC table.
--
INSERT /*+ APPEND */ INTO sales_ac SELECT * FROM sales_source
/
--
REM Observe the addition of "SORT ORDER BY" in the execution plan
--
SELECT * FROM TABLE(dbms_xplan.display_cursor)
/
COMMIT
/
set timing off
REM Gather table statistics
EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'sales')
EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'sales_ac')
Führt man den Beispielcode aus, kann man sehen, dass das Schreiben mittels INSERT APPEND in die Zieltabelle bei der SALES_AC-Tabelle messbar länger dauert, da im Ausführungsplan des INSERTs eine entsprechende SORT ORDER BY-Operation enthalten ist, die das Attribute Clustering abbildet. Das Feature kostet also mehr Zeit beim Schreiben der Daten. Es benötigt – wie schon zuvor erwähnt – diese Spezialoperation INSERT APPEND, damit das Attribute Clustering dabei berücksichtigt wird – ein konventionelles INSERT würde dies nicht tun, wie beim ersten Beispiel gezeigt.
Das Attribute Clustering wurde hier nun für die zwei Fremdschlüssel LOCATION_ID und PRODUCT_ID zu den beiden vorab erzeugten Dimensionen angelegt und in diesem Fall als "Interleaved Order", was bedeutet, dass gemäß beider Attribute unabhängig voneinander die Daten zusammenhängend in der Tabelle abgelegt werden. Es werden nun also sowohl Abfragen nur auf der LOCATION_ID als auch nur auf der PRODUCT_ID durch das Attribute Clustering optimiert. Dies ist, wie bereits erwähnt, mit den anderen Methoden, wie zum Beispiel einer Index Organized Table so nicht möglich und ein Alleinstellungsmerkmal des Attribute-Clustering-Features.
Führt man nun entsprechende Abfragen auf den beiden Tabellen aus, die auf die genannten Attribute filtern, kann man den Unterschied zwischen den beiden Tabellen in der Anzahl der notwendigen "logischen" I/Os deutlich sehen. Zuerst werden die dafür benötigten Indizes erzeugt:
SQL> REM Create indexes on location id for the standard SALES
SQL> REM table and the attribute clustered SALES_AC table
SQL>
SQL> CREATE INDEX sales_loc_i ON sales (location_id, product_id)
2 /
Index created.
SQL> CREATE INDEX sales_ac_loc_i ON sales_ac (location_id, product_id)
2 /
Index created.
SQL> CREATE INDEX sales_prod_i ON sales (product_id)
2 /
Index created.
SQL> CREATE INDEX sales_ac_prod_i ON sales_ac (product_id)
2 /
Index created.
SQL> REM Observe the improved value of "Average Blocks Per Key"
SQL> REM for the attribute clustered table. This will
SQL> REM result in fewer consistend gets for table lookups from
SQL> REM index range scans.
SQL>
SQL> SELECT index_name, clustering_factor,avg_data_blocks_per_key
2 FROM user_indexes
3 WHERE index_name LIKE 'SALES%LOC%'
4 ORDER BY index_name
5 /
INDEX_NAME CLUSTERING_FACTOR AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------------- -----------------------
SALES_AC_LOC_I 102182 1
SALES_LOC_I 1950703 22
2 rows selected.
Dabei wird schon anhand der unterschiedlichen Kennzahl "Clustering Factor" der Indizes auf den beiden Tabellen ersichtlich, dass die Daten in der Tabelle mit Attribute Clustering deutlich besser der Sortierung des Indexes entspricht als bei der Tabelle ohne Attribute Clustering – der Wert ist ca. 100.000 bei der einen und fast zwei Millionen bei der anderen Tabelle. Um so höher der Wert und um so mehr er sich der Anzahl der Zeilen in der Tabelle annähert, um so schlechter stimmen Tabelle und Index von der Sortierung her überein. Der Wert sagt im Grunde aus, wie viele verschiedene Blöcke der Tabelle angesprochen werden müssen, wenn man alle Zeilen der Tabelle in der Reihenfolge des Indexes ausliest. Im Idealfall, wenn also Tabelle und Index die exakt gleiche Sortierung haben, entspricht der Wert der Anzahl der Blöcke der Tabelle, da dann jeder Block genau nur einmal angesprochen werden muss. Im schlechtesten Fall entspricht der Wert der Anzahl der Zeilen der Tabelle, die über den Index angesprochen werden können, da dann für jede Zeile auf einen anderen Block der Tabelle zugegriffen werden muss.
Nun können die eigentlichen Abfragen auf den beiden Tabellen ausgeführt werden:
SQL> SET AUTOTRACE ON
SQL>
SQL> REM Observe the IO differences for clustered table.
SQL>
SQL> set termout off
SQL>
SQL> REM Nonclustered
SQL>
SQL> SELECT SUM(amount)
2 FROM sales
3 WHERE location_id = 50
4 /
SUM(AMOUNT)
-----------
604281,39
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1930106867
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 625 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 621 | 5589 | 625 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_LOC_I | 621 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOCATION_ID"=50)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
598 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM With attribute clustering
SQL>
SQL> SELECT SUM(amount)
2 FROM sales_ac
3 WHERE location_id = 50
4 /
SUM(AMOUNT)
-----------
604281,39
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 311709933
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 37 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC | 621 | 5589 | 37 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_AC_LOC_I | 621 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOCATION_ID"=50)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM Query on both location_id and product_id
SQL>
SQL> set termout off
SQL>
SQL> REM Nonclustered
SQL>
SQL> SELECT SUM(amount)
2 FROM sales
3 WHERE location_id = 50
4 AND product_id = 10
5 /
SUM(AMOUNT)
-----------
30103,56
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1930106867
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 27 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 23 | 276 | 27 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_LOC_I | 23 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOCATION_ID"=50 AND "PRODUCT_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM With attribute clustering
SQL>
SQL> SELECT SUM(amount)
2 FROM sales_ac
3 WHERE location_id = 50
4 AND product_id = 10
5 /
SUM(AMOUNT)
-----------
30103,56
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 311709933
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC | 23 | 276 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_AC_LOC_I | 23 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOCATION_ID"=50 AND "PRODUCT_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM In this case we have used an attribute cluster with Interleaved ordering
SQL> REM so we can use predicates on location_id or product_id (or both)
SQL>
SQL> REM Forcing index usage here because otherwise the optimizer would use
SQL> REM a full table scan on the table without clustering
SQL> REM and observe the reduced number of consistent
SQL> REM gets for the attribute cluster example.
SQL>
SQL> REM Nonclustered
SQL>
SQL> SELECT /*+ index(sales) */ SUM(amount)
2 FROM sales
3 WHERE product_id = 10
4 /
SUM(AMOUNT)
-----------
72522121,4
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 873694340
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 16640 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 72684 | 567K| 16640 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_PROD_I | 72684 | | 145 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PRODUCT_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16069 consistent gets
144 physical reads
0 redo size
353 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> REM With interleaved attribute clustering
SQL>
SQL> SELECT /*+ index(sales_ac) */ SUM(amount)
2 FROM sales_ac
3 WHERE product_id = 10
4 /
SUM(AMOUNT)
-----------
72522121,4
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 266287893
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 796 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES_AC | 72684 | 567K| 796 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SALES_AC_PROD_I | 72684 | | 145 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PRODUCT_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
795 consistent gets
0 physical reads
0 redo size
353 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE OFF
SQL>
SQL> REM Drop the test indexes
SQL>
SQL> DROP INDEX sales_loc_i
2 /
Index dropped.
SQL> DROP INDEX sales_ac_loc_i
2 /
Index dropped.
SQL> DROP INDEX sales_prod_i
2 /
Index dropped.
SQL> DROP INDEX sales_ac_prod_i
2 /
Index dropped.
Wie man sehen kann, benötigt die Tabelle mit Attribute Clustering in allen Beispielen – egal ob auf LOCATION_ID oder PRODUCT_ID oder beides gefiltert wird – deutlich weniger logisches I/O ("consistent gets"-Statistik) als die Tabelle ohne Attribute Clustering. Das Besondere an dem Beispiel ist eben, dass beide Attribute LOCATION_ID und PRODUCT_ID unabhängig voneinander als Filter verwendet werden können, und beide Zugriffswege durch das "Interleaved Ordering" des Attribute Clusterings optimiert werden.
Lesen Sie den dritten Teil des Artikels hier...
