Attribute Clustering, unterschätztes Oracle-Feature (III)
Dies ist der dritte Teil des Artikels. Lesen Sie den ersten Teil hier.
Join Attribute Clustering
Nun verwenden echte Abfragen in einem typischen Star-Schema selten Filter direkt auf den Fremdschlüsseln der Faktentabelle, sondern filtern meistens auf den Dimensionen. Hier würden also Joins zwischen Dimensions- und Faktentabelle stattfinden und die Filter entsprechend auf den Dimensionen angewendet werden.
Selbst dieses Szenario kann mittels Attribute Clustering optimiert werden, und dies mittels der Kombination von Join Attribute Clustering und Interleaved Order sogar für mehrere Zugriffswege unabhängig voneinander.
Das heißt, es ist sogar möglich, Joins zu optimieren, also den Zugriff auf eine Tabelle per Join von einer anderen Tabelle durch Attribute Clustering zu optimieren. Dies ist der Funktionalität von Oracle-Clustern ähnlich, in dem Sinne, dass die Daten so abgelegt werden, dass Joins zwischen den Tabellen möglichst effizient durchgeführt werden können. Nur werden hier nicht die Daten von unterschiedlichen Tabellen in den gleichen Blöcken abgelegt (Oracle Table Cluster mit mehreren Tabellen), sondern nur entsprechend innerhalb einer Heap-Tabelle organisiert.
Als Voraussetzung dafür müssen zumindest Unique Indizes auf den per Join verknüpften Tabellen existieren, die garantieren, dass der Join keine Duplikate erzeugt. Idealerweise geht Oracle von einem Star- oder Snowflake-Schema mit einer Fakten- und mehreren Dimensionstabellen aus. Das Feature ist also auch an dieser Stelle an Data-Warehouse-Umgebungen angelehnt.
Wie würde das nun in dem konkreten Beispiel aussehen können? Anstatt das Attribute Clustering direkt auf den Fremdschlüsseln zu definieren, wird nun ein entsprechend zu optimierender "Join" im Clustering festgelegt:
SQL> REM Drop the current attribute clustering definition
SQL>
SQL> ALTER TABLE sales_ac DROP CLUSTERING
2 /
Table altered.
SQL> TRUNCATE TABLE sales_ac
2 /
Table truncated.
SQL> --
SQL> REM Enable interleaved join attribute clustering
SQL> REM on SALES_AC table.
SQL> REM For the sake of example, create
SQL> REM the zone map manually.
SQL> --
SQL> ALTER TABLE sales_ac
2 ADD CLUSTERING sales_ac
3 JOIN locations ON (sales_ac.location_id = locations.location_id)
4 JOIN products ON (sales_ac.product_id = products.product_id)
5 BY INTERLEAVED ORDER ((locations.state, locations.county),products.product_name)
6 --BY LINEAR ORDER (locations.state, locations.county)
7 WITHOUT MATERIALIZED ZONEMAP
8 /
Table altered.
SQL> --
SQL> REM Manually create the zone map
SQL> --
SQL>
SQL> CREATE MATERIALIZED ZONEMAP sales_ac_zmap
2 AS
3 SELECT SYS_OP_ZONE_ID(s.rowid),
4 MIN(l.state) min_state,
5 MAX(l.state) max_state,
6 MIN(l.county) min_county,
7 MAX(l.county) max_county,
8 MIN(p.product_name) min_prod,
9 MAX(p.product_name) max_prod
10 FROM sales_ac s,
11 locations l,
12 products p
13 WHERE s.location_id = l.location_id(+)
14 AND s.product_id = p.product_id(+)
15 GROUP BY SYS_OP_ZONE_ID(s.rowid)
16 /
GROUP BY SYS_OP_ZONE_ID(s.rowid)
*
ERROR at line 15:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type
SQL> --
SQL> REM Insert data and observe that a
SQL> REM sorts and joins are performed to cluster
SQL> REM data in the SALES_AC table.
SQL> REM The direct path insert operation will maintain
SQL> REM the zone map for us.
SQL> --
SQL> INSERT /*+ APPEND */ INTO sales_ac SELECT * FROM sales_source
2 /
1952104 rows created.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor)
2 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8wzqwqgwwynya, child number 0
-------------------------------------
INSERT /*+ APPEND */ INTO sales_ac SELECT * FROM sales_source
Plan hash value: 2808338825
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 47632 (100)| |
| 1 | LOAD AS SELECT | SALES_AC | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1952K| 180M| | 47632 (1)| 00:00:02 |
| 3 | SORT ORDER BY | | 1952K| 180M| 203M| 47632 (1)| 00:00:02 |
|* 4 | HASH JOIN RIGHT OUTER | | 1952K| 180M| | 4408 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | LOCATIONS | 3143 | 94290 | | 11 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 1952K| 124M| | 4388 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PRODUCTS | 28 | 364 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SALES_SOURCE | 1952K| 100M| | 4378 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SALES_SOURCE"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
6 - access("SALES_SOURCE"."PRODUCT_ID"="PRODUCTS"."PRODUCT_ID")
26 rows selected.
SQL> COMMIT
2 /
Commit complete.
SQL> EXECUTE dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'sales_ac');
PL/SQL procedure successfully completed.
Die Definition des Attribute Clusterings wurde also auf der Tabelle SALES_AC wieder entsprechend geändert. Es wird nun zu den Dimensionstabellen LOCATIONS und PRODUCTS jeweils ein Join definiert und der Zugriff auf Filtern auf den folgenden Attributen der Dimensionstabellen optimiert:
(locations.state, locations.county)
products.product_nameUnd zwar unabhängig voneinander – es werden jetzt also Abfragen optimiert, die die SALES_AC-Tabelle mit der LOCATIONS-Tabelle per Join auf der LOCATION_ID verknüpfen und auf der LOCATIONS-Dimension auf STATE oder STATE und COUNTY filtern, als auch Abfragen, die die SALES_AC-Tabelle mit der PRODUCTS-Tabelle per Join auf der PRODUCT_ID verknüpfen und auf der PRODUCTS-Dimension auf PRODUCT_NAME filtern.
Das Beispiel beinhaltet der Vollständigkeit halber auch die Definition einer sogenannten "Zonemap" – dies ist allerdings nur auf exadata-basierten Umgebungen (Exadata on premise, Exadata Cloud at Customer, Autonomous Database on Exadata) aus lizenztechnischen Gründen unterstützt, was sehr schade ist, da dieses Feature gerade auf Nicht-Exadata-Umgebungen sehr nützlich wäre. Damit könnten dann unter anderem auch Full-Table-Scan-Zugriffe mittels Attribute Clustering optimiert werden, indem die Datenbank dabei nur auf die Blöcke der Tabelle zugreift, in denen laut Zonemap Information auch Zeilen stehen, die die gesuchten Werte beinhalten – der Rest kann einfach übersprungen werden. Effizient wird das dann, wenn die Daten eben gemäß des Suchkriteriums zusammenhängend in der Tabelle mittels Attribute Clusterings organisiert sind.
Zurück zu den Abfragen mit optimierten Joins. Zuerst werden wieder passende Indizes auf der Faktentabelle erzeugt:
SQL> REM The full potential of attribute clusters are realised
SQL> REM when used in conjunction with zone maps, Exadata storage indexes
SQL> REM and In-Memory min/max pruning. However, they also improve
SQL> REM index clustering. This is demonstrated here.
SQL>
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)
2 /
Index created.
SQL> CREATE INDEX sales_ac_loc_i ON sales_ac (location_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.
Dann können die Beispiel-Abfragen ausgeführt werden, und zwar wieder auf beiden Tabellen exemplarisch:
SQL> SET AUTOTRACE ON
SQL>
SQL> REM Conventional
SQL>
SQL> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales
3 JOIN locations ON (sales.location_id = locations.location_id)
4 WHERE locations.state = 'California'
5 /
SUM(AMOUNT)
-----------
35796188,2
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2388624724
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4410 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | HASH JOIN | | 36024 | 809K| 4410 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LOCATIONS | 58 | 812 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SALES | 1952K| 16M| 4390 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALES"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
3 - filter("LOCATIONS"."STATE"='California')
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16135 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> REM With attribute clustering
SQL>
SQL> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales_ac
3 JOIN locations ON (sales_ac.location_id = locations.location_id)
4 WHERE locations.state = 'California'
5 /
SUM(AMOUNT)
-----------
35796188,2
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 918547280
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 2042 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
| 2 | NESTED LOOPS | | 36024 | 809K| 2042 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 36024 | 809K| 2042 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | LOCATIONS | 58 | 812 | 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SALES_AC_LOC_I | 621 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES_AC | 621 | 5589 | 35 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("LOCATIONS"."STATE"='California')
5 - access("SALES_AC"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2127 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> REM Conventional
SQL>
SQL> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales
3 JOIN locations ON (sales.location_id = locations.location_id)
4 WHERE locations.state = 'California'
5 AND locations.county = 'Alpine County'
6 /
SUM(AMOUNT)
-----------
643823,06
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1895572434
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 623 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | 17 | 663 | 623 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 621 | 663 | 623 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | LOCATIONS | 1 | 30 | 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SALES_LOC_I | 621 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES | 621 | 5589 | 612 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("LOCATIONS"."COUNTY"='Alpine County' AND
"LOCATIONS"."STATE"='California')
5 - access("SALES"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
655 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> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales_ac
3 JOIN locations ON (sales_ac.location_id = locations.location_id)
4 WHERE locations.state = 'California'
5 AND locations.county = 'Alpine County'
6 /
SUM(AMOUNT)
-----------
643823,06
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 918547280
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | 17 | 663 | 46 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 621 | 663 | 46 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | LOCATIONS | 1 | 30 | 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SALES_AC_LOC_I | 621 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES_AC | 621 | 5589 | 35 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("LOCATIONS"."COUNTY"='Alpine County' AND "LOCATIONS"."STATE"='California')
5 - access("SALES_AC"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
72 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 Since interleaved ordering was used to cluster the table,
SQL> REM predicates can be used in various combinations. In particular
SQL> REM pruning is still effective if product_name is used alone.
SQL> REM Predicates for location dimensions do not need to be included.
SQL>
SQL> REM Conventional
SQL>
SQL> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales
3 JOIN products ON (sales.product_id = products.product_id)
4 WHERE products.product_name = 'DATEPALM'
5 /
SUM(AMOUNT)
-----------
36235437
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2961462735
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 4397 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | HASH JOIN | | 69718 | 1429K| 4397 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| PRODUCTS | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SALES | 1952K| 14M| 4386 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALES"."PRODUCT_ID"="PRODUCTS"."PRODUCT_ID")
3 - filter("PRODUCTS"."PRODUCT_NAME"='DATEPALM')
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
16102 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> REM With attribute clustering
SQL>
SQL> set termout off
SQL>
SQL> SELECT SUM(amount)
2 FROM sales_ac
3 JOIN products ON (sales_ac.product_id = products.product_id)
4 WHERE products.product_name = 'DATEPALM'
5 /
SUM(AMOUNT)
-----------
36235437
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1999596888
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 745 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | NESTED LOOPS | | 69718 | 1429K| 745 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 69718 | 1429K| 745 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | PRODUCTS | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SALES_AC_PROD_I | 69718 | | 137 (1)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES_AC | 69718 | 544K| 743 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("PRODUCTS"."PRODUCT_NAME"='DATEPALM')
5 - access("SALES_AC"."PRODUCT_ID"="PRODUCTS"."PRODUCT_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
407 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 beschrieben verknüpfen die Beispiel-Abfragen die Faktentabelle mit der LOCATIONS- oder PRODUCTS-Tabelle und filtern auf den entsprechenden Attributen der Dimension. Alle gezeigten Szenarien benötigen wieder deutlich weniger logisches I/O (consistent gets-Statistik) auf der Tabelle SALES_AC mit dem definierten "Join Attribute Clustering", und zwar eben auch wieder für unterschiedliche Zugriffswege, sowohl der Join zu der LOCATIONS-Dimension und dem Filter auf der STATE- oder STATE- und COUNTY-Spalte, als auch unabhängig davon der Join zu der PRODUCTS-Dimension und dem Filter auf der PRODUCTS_NAME-Spalte.
Zusammenfassung
Es ist also mittels Attribute Clusterings möglich, sowohl einfache Zugriffe auf einer Tabelle per Index zu optimieren, als auch komplexere Szenarien, die Joins zwischen Fakten- und Dimensionstabellen beinhalten. Eine weitere Besonderheit des Attribute Clusterings ist die Möglichkeit, mehr als einen Zugriffsweg auf eine Tabelle optimieren zu können – im Rahmen gewisser Grenzen.
Außerdem sei angemerkt, dass mittels der Zonemaps nicht zur Index-Zugriffe mittels Attribute Clusterings optimiert werden können, wenn man sich auf einer unterstützten Plattform befindet. Durch das Attribute Clustering verbessern sich potentiell auch noch andere Effekte, wie zum Beispiel die Komprimierung von Daten bei Verwendung der verschiedenen Kompressionsoptionen von Oracle. Insbesondere auch bei der Verwendung der Inmemory Column Store-Option von Oracle, bei der die Daten auch komprimiert im Speicher abgelegt werden können.
Auch das sogenannte "Inmemory Pruning", das im Grunde auf der gleichen Information beruht, wie die erwähnte Zonemap, kann mittels Attribute Clustering verbessert werden. Oracle muss also beim Inmemory Scan effektiv nur die Daten im Speicher scannen, die das gesuchte Kriterium beinhalten, alles andere kann wieder übersprungen werden.
