SQL Abfragen auf OLAP Cubes, Teil 2
von Frank Schneede, ORACLE Deutschland GmbH

In der letzten Ausgabe wurden Abfragen mit Standard SQL auf einen OLAP Cube ausführlich beschrieben. Die dort vorgestellte Abfragetechnik verwendet nur die relationalen Cube Views, die im Zuge der Generierung des OLAP Cubes im Analytic Workspace Manager (AWM) automatisch erzeugt werden. Die direkte Abfrage der relationalen Cube Views hat hierbei unmittelbar Auswirkungen auf die Formulierung des SQL-Statements. Es dürfen in dieser Art von Abfrage nämlich keine Aggregat- oder Gruppierungsfunktionen mehr verwendet werden. Das Ergebnis der Aggregationen ist direkt im Cube - oder besser der Cube View - als Spalte enthalten. Eine Abfragetechnik unter Verwendung der Cube Views würde sich also demnach für ein eigenes Reporting von Endbenutzern anbieten, da die zu erstellenden Statements wenig Komplexität besitzen und sehr einfach formuliert werden können. Besondere SQL Kenntnisse sind an dieser Stelle nicht notwendig.

Die in diesem zweiten Teil vorgestellte Abfragetechnik verwendet sogenannte Cube-organized Materialized Views, auf die mit komplexen SQL-Statements und mit Verwendung von Query Rewrite zugegriffen wird. Diese Art von Statements enthalten Aggregat- sowie auch Gruppierungsfunktionen und werden häufig durch BI Abfragetools erzeugt. Ein Berichtsgenerator wird ebenfalls eher mit Aggregat- und Gruppierungsfunktionen auf die Tabellen des zugrundeliegenden STAR Schemas zugreifen und auf diese Weise die Vorteile des Query Rewrites nutzen.

Die Erstellung der in Oracle 11g eingeführten Cube-organized Materialized Views im Analytic Workspace Manager (AWM), deren Konfigurationsmöglichkeiten und schließlich die Erstellung von Abfragen wird in diesem Tipp anhand von Beispielen demonstriert. Wie bereits der letzte Tipp ist auch dieser wieder so gestaltet, dass Sie die einzelnen Schritte selbst nachvollziehen sollten, um das komplexe Thema besser nachvollziehen zu können.

Im ersten Schritt müssen die Voraussetzungen geschaffen werden, um die in diesem Tipp verwendeten SQL Statements ausführen zu können. Das geschieht, indem der Tipp Erstellung von OLAP Cubes leicht gemacht mit dem Analytic Workspace Manager 11g (AWM) oder alternativ die Schritte

  1. Installation der Oracle Datenbank 11g mit OLAP Option
  2. Installation des Analytic Workspace Manager 11g
  3. Installation des Beispiel-Schemas
ausgeführt werden.

Zum besseren Verständnis soll an dieser Stelle nocheinmal die Struktur des Schemas vorgestellt werden, das für die Abfragen verwendet wird. Die dem OLAP Cube zugrunde liegenden Tabellen bilden ein sogenanntes STAR Schema. Es besteht aus den Tabellen
  • CHANNELS - (Dimensions-)Tabelle mit Vertriebskanälen (Shops, Web-Shops)
  • CUSTOMERS - (Dimensions-)Tabelle mit Kundeninformationen
  • PRODUCTS - (Dimensions-)Tabelle für Produktinformationen (Lieferant, Abteilung, ...)
  • TIMES - (Dimensions-)Tabelle mit Zuordnung der Kalendertage zu unterschiedlichen Zeitabschnitten (Kalenderjahr, Quartal, ...)
  • SALES_FACT - (Fakten-)Tabelle aller Verkäufe mit Menge, Stückpreis, Gesamtpreis und Dimensionsinformationen (Datum, Produkt, Kunde, Vertriebskanal)
Die Dimensionstabellen sind jeweils mit der Faktentabelle über referentielle Integritäten verbunden, die man als Foreign Key Constraints in der Datenbank abbildet. Auf die oben genannten Tabellen wird in den später formulierten Abfragen zugegriffen. Doch zuerst muss für die Erstellung der Abfragen die Basis gelegt werden, das heißt, im folgenden Abschnitt wird die Technik der Cube-organized Materialized View beschrieben.

Verwendung von Cube-organized Materialized Views

Seit der Oracle Version 11g besteht die Möglichkeit, über den OLAP Cube eine sogenannte Cube-organized Materialized View zu legen. Der SQL Query Optimizer erkennt automatisch, wann diese Materialized View bei einer Abfrage auf die Faktentabelle genutzt werden kann und schreibt (Query Rewrite) das SQL Statement entsprechend um. Über Cube-organized Materialized Views kann somit nicht nur eine Verbesserung in Hinblick auf die Verwaltung von Aggregattabellen erreicht werden, auch die Abfrage Performance wird signifikant verbessert.

In diesem Abschnitt wird nun gezeigt, wie eine Cube-organized Materialized View mit der Query Rewrite Funktionalität eingerichtet wird. Zum Abschluß wird dann durch die Ausführung mehrerer Statements und der Analyse der zugehörigen Ausführungspläne gezeigt, welchen Performancevorteil Cube-organized Materialized Views bieten.

Erstellung der Cube-organized Materialized View und des Query Rewrites

Die Erstellung erfolgt wie im letzten Artikel zum Thema beschrieben, durch Setzen der Check-Boxes im Analytic Workspace Manager (AWM). Die Checkbox für die Aktivierung des Query Rewrites muss separat gesetzt werden. Nach Drücken des Buttons Anwenden wird die Erzeugung angestoßen, die in Abhängigkeit von der Datenmenge und der Datenkomplexität eine Weile dauern kann. Die folgenden Screenshots zeigen den Ablauf
Für eine größere Ansicht auf das betreffende Bild klicken


Eine Cube-organized Materialized View belegt keinen zusätzlichen Speicherplatz, sondern ist lediglich eine Registrierung im Data Dictionary. Damit verhält sie sich wie die bereits aus der relationalen Welt bekannten Materialized Views on prebuilt table - über den OLAP Cube hinaus werden keine redundanten Daten erzeugt. Das folgende SELECT Statement zeigt den Platzbedarf des Schemas OLAPTRAIN vor und nach der Anlage der Materialized View. 
SQL> -- vor dem create mav
SQL> SELECT sum(bytes)/1024/1024
    ,      count(1)
     FROM user_segments;

SUM(BYTES)/1024/1024   COUNT(1)
-------------------- ----------
            1690,125        476

SQL> SELECT count(1)
     ,      object_type
     FROM user_objects
     GROUP BY object_type;

  COUNT(1) OBJECT_TYPE
---------- -------------------
       244 INDEX PARTITION
        32 TABLE SUBPARTITION
         2 SEQUENCE
       113 TABLE PARTITION
         4 LOB PARTITION
         5 LOB
        32 INDEX SUBPARTITION
         4 DIMENSION
         4 MATERIALIZED VIEW
        29 TABLE
        10 INDEX
         9 VIEW
         4 CUBE DIMENSION
        32 LOB SUBPARTITION
         1 CUBE
         2 TYPE

16 Zeilen ausgewählt.

SQL>
.
.
.
SQL> -- nach dem create mav
SQL> SELECT sum(bytes)/1024/1024
     ,      count(1)
     FROM user_segments;

SUM(BYTES)/1024/1024   COUNT(1)
-------------------- ----------
            1690,125        476

SQL> SELECT count(1)
     ,      object_type
     FROM user_objects
     GROUP BY object_type;

  COUNT(1) OBJECT_TYPE
---------- -------------------
       244 INDEX PARTITION
        32 TABLE SUBPARTITION
         2 SEQUENCE
       113 TABLE PARTITION
         4 LOB PARTITION
         5 LOB
        32 INDEX SUBPARTITION
         4 DIMENSION
         5 MATERIALIZED VIEW
        31 TABLE
        10 INDEX
         9 VIEW
         4 CUBE DIMENSION
        32 LOB SUBPARTITION
         1 CUBE
         2 TYPE

16 Zeilen ausgewählt.

SQL>
Der Status einer Materialized View kann über die Spalte STALENESS in der View DBA_MVIEWS abgefragt werden. Sie gibt Auskunft über die Aktualität der Daten in der Materialized View. Wenn der Status UNUSABLE sein sollte, ist eine vollständige Aktualisierung der Materialized View erforderlich, in anderen Fällen, in denen der Status nicht FRESH ist, reicht ein inkrementeller Refresh, um das Query Rewrite nutzen zu können. 
SQL> SELECT mview_name
     ,      updatable
     ,      rewrite_enabled
     ,      refresh_mode
     ,      refresh_method
     ,      build_mode
     ,      fast_refreshable
     ,      last_refresh_type
     ,      last_refresh_date
     ,      staleness
     ,      compile_state
     FROM user_mviews
     WHERE mview_name like '%CUBE';

MVIEW_NAME                     U R REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE   LAST_REF LAST_REF STALENESS           COMPILE_STATE
------------------------------ - - ------ -------- --------- ------------------ -------- -------- ------------------- -------------------
CB$SALES_CUBE                  N Y DEMAND FORCE    DEFERRED  DIRLOAD_LIMITEDDML NA                UNUSABLE            VALID

SQL> set timing on
SQL> execute dbms_mview.refresh('CB$SALES_CUBE','F');
BEGIN dbms_mview.refresh('CB$SALES_CUBE','F'); END;

*
FEHLER in Zeile 1:
ORA-12057: Materialized View "OLAPTRAIN"."CB$SALES_CUBE" ist UNGÜLTIG und muss vollständig abgeglichen werden
ORA-06512: in "SYS.DBMS_SNAPSHOT", Zeile 2545
ORA-06512: in "SYS.DBMS_SNAPSHOT", Zeile 2751
ORA-06512: in "SYS.DBMS_SNAPSHOT", Zeile 2720
ORA-06512: in Zeile 1


Abgelaufen: 00:00:01.09
SQL> execute dbms_mview.refresh('CB$SALES_CUBE','C');

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:12:20.29
SQL> SELECT mview_name
     ,      updatable
     ,      rewrite_enabled
     ,      refresh_mode
     ,      refresh_method
     ,      build_mode
     ,      fast_refreshable
     ,      last_refresh_type
     ,      last_refresh_date
     ,      staleness
     ,      compile_state 
      FROM user_mviews
      WHERE mview_name like '%CUBE';

MVIEW_NAME                     U R REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE   LAST_REF LAST_REF STALENESS           COMPILE_STATE
------------------------------ - - ------ -------- --------- ------------------ -------- -------- ------------------- -------------------
CB$SALES_CUBE                  N Y DEMAND FORCE    DEFERRED  DIRLOAD_LIMITEDDML FAST_PCT 21.09.09 FRESH               VALID

SQL>

Ausführen von Statements ohne Query Rewrite

Die Ausführung von 4 verschiedenen Statements, die direkt auf die Originaltabellen zugreifen, soll jetzt zeigen, wie die Performance durch den Einsatz einer Cube-organized Materialized View gesteigert werden kann. Die Statements werden im Schema OLAPTRAIN ausgeführt. Die Statements in Skriptform finden Sie hier.

Im ersten Beispiel werden die Dimensionstabellen TIMES und PRODUCTS mit der Faktentabelle SALES_FACT verknüpft, um die aggregierten Verkäufe je Kategorie und Kalenderjahr auszuwerten. Der Ausführungsplan zeigt jeweils Full Table Scans und Hash-Joins, wobei der Join partitionsweise ausgeführt wird. 
SQL> ALTER MATERIALIZED VIEW cb$sales_cube DISABLE QUERY REWRITE;

Materialized View wurde geändert.

SQL>
SQL> -- 1. Quantity and Sales by Year and Product Category
SQL> COLUMN quantity FORMAT 999,990.000
SQL> COLUMN sales    FORMAT 999,999,990.000
SQL> COLUMN cy       FORMAT a10
SQL> COLUMN cat      FORMAT a35
SQL> EXPLAIN PLAN FOR
  2  SELECT SUM(s.quantity)      AS quantity
  3  ,      SUM(s.sales)         AS sales
  4  ,      t.calendar_year_name AS cy
  5  ,      p.category_name      AS cat
  6  FROM  times      t
  7  ,     products   p
  8  ,     sales_fact s
  9  WHERE p.item_key = s.product
 10  AND   s.day_key  = t.day_key
 11  GROUP BY p.category_name
 12  ,        t.calendar_year_name;

EXPLAIN PLAN ausgeführt.

SQL>
SQL> @c:\oracle\product\11.1.0\rdbms\admin\utlxplp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 30947829

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |    23 |  1472 |  4198   (5)| 00:00:51 |       |       |
|   1 |  HASH GROUP BY                 |            |    23 |  1472 |  4198   (5)| 00:00:51 |       |       |
|*  2 |   HASH JOIN                    |            |  2811K|   171M|  4058   (2)| 00:00:49 |       |       |
|   3 |    TABLE ACCESS FULL           | PRODUCTS   |  2713 | 81390 |    40   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                   |            |  2811K|    91M|  4003   (2)| 00:00:49 |       |       |
|   5 |     PART JOIN FILTER CREATE    | :BF0000    |  1461 | 21915 |    66   (0)| 00:00:01 |       |       |
|   6 |      PARTITION RANGE ALL       |            |  1461 | 21915 |    66   (0)| 00:00:01 |     1 |1048575|
|   7 |       TABLE ACCESS FULL        | TIMES      |  1461 | 21915 |    66   (0)| 00:00:01 |     1 |1048575|
|   8 |     PARTITION RANGE JOIN-FILTER|            |  2811K|    50M|  3922   (1)| 00:00:48 |:BF0000|:BF0000|
|   9 |      TABLE ACCESS FULL         | SALES_FACT |  2811K|    50M|  3922   (1)| 00:00:48 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."ITEM_KEY"="S"."PRODUCT")
   4 - access("S"."DAY_KEY"="T"."DAY_KEY")

22 Zeilen ausgewählt.

SQL>
SQL> SELECT SUM(s.quantity)      AS quantity
  2  ,      SUM(s.sales)         AS sales
  3  ,      t.calendar_year_name AS cy
  4  ,      p.category_name      AS cat
  5  FROM  times      t
  6  ,     products   p
  7  ,     sales_fact s
  8  WHERE p.item_key = s.product
  9  AND   s.day_key  = t.day_key
 10  GROUP BY p.category_name
 11  ,        t.calendar_year_name;

    QUANTITY            SALES CY         CAT
------------ ---------------- ---------- -----------------------------------
     156.000       62,522.000 CY2005     All Computer Furniture
     411.000       93,239.610 CY2006     PDAs
 145,934.000   16,264,253.090 CY2006     Total iPlayer Family
  79,348.000    4,515,305.600 CY2006     Camcorders and Accessories
  72,008.000    4,068,490.800 CY2005     Camcorders and Accessories
.
.
.
  91,317.000   15,663,215.050 CY2005     Computer Printers and Supplies
 135,179.000   15,102,590.700 CY2005     Total iPlayer Family
 570,861.000   91,680,172.610 CY2006     Total Personal Computers
  99,579.000   18,339,588.070 CY2006     Computer Printers and Supplies
   4,846.000    1,938,230.600 CY2006     Total Server Computers

24 Zeilen ausgewählt.

Abgelaufen: 00:00:06.43
SQL>
In der zweiten Abfrage kommen die zwei anderen Dimensionstabellen CHANNELS und CUSTOMERS hinzu, die Gruppierung erfolgt zusätzlich zu dem oben ausgeführten Statement nach Klasse und Land. Der Ausführungsplan ähnelt dem oben gezeigten Plan und arbeitet ebenfalls mit Full Table Scans, Hash Joins und einem partitionsweisen Join. 
SQL> -- 2. Quantity and Sales by Year, Department, Class and Country
SQL> COLUMN dept      FORMAT a30
SQL> COLUMN class     FORMAT a30
SQL> COLUMN country   FORMAT a30
SQL> EXPLAIN PLAN FOR
  2  SELECT SUM(s.quantity)      AS quantity
  3  ,      SUM(s.sales)         AS sales
  4  ,      t.calendar_year_name AS cy
  5  ,      p.department_name    AS dept
  6  ,      c.class_name         AS class
  7  ,      cu.country_name      AS country
  8  FROM  times      t
  9  ,     products   p
 10  ,     channels   c
 11  ,     customers  cu
 12  ,     sales_fact s
 13  WHERE p.item_key = s.product
 14  AND   s.day_key  = t.day_key
 15  AND   s.channel  = c.channel_key
 16  AND   s.customer = cu.customer_key
 17  GROUP BY p.department_name
 18  ,        t.calendar_year_name
 19  ,        c.class_name
 20  ,        cu.country_name;

EXPLAIN PLAN ausgeführt.

SQL>
SQL> @c:\oracle\product\11.1.0\rdbms\admin\utlxplp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3596963109

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |   544 | 46240 |       | 14773   (2)| 00:02:58 |       |       |
|   1 |  HASH GROUP BY                   |            |   544 | 46240 |       | 14773   (2)| 00:02:58 |       |       |
|*  2 |   HASH JOIN                      |            |  2811K|   227M|       | 14634   (1)| 00:02:56 |       |       |
|   3 |    TABLE ACCESS FULL             | PRODUCTS   |  2713 | 46121 |       |    40   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |            |  2811K|   182M|       | 14579   (1)| 00:02:55 |       |       |
|   5 |     PART JOIN FILTER CREATE      | :BF0000    |  1461 | 21915 |       |    66   (0)| 00:00:01 |       |       |
|   6 |      PARTITION RANGE ALL         |            |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|   7 |       TABLE ACCESS FULL          | TIMES      |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|*  8 |     HASH JOIN                    |            |  2811K|   142M|       | 14498   (1)| 00:02:54 |       |       |
|   9 |      TABLE ACCESS FULL           | CHANNELS   |    13 |   143 |       |     3   (0)| 00:00:01 |       |       |
|* 10 |      HASH JOIN                   |            |  2811K|   112M|  6680K| 14480   (1)| 00:02:54 |       |       |
|  11 |       TABLE ACCESS FULL          | CUSTOMERS  |   253K|  3707K|       |  5014   (1)| 00:01:01 |       |       |
|  12 |       PARTITION RANGE JOIN-FILTER|            |  2811K|    72M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
|  13 |        TABLE ACCESS FULL         | SALES_FACT |  2811K|    72M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."ITEM_KEY"="S"."PRODUCT")
   4 - access("S"."DAY_KEY"="T"."DAY_KEY")
   8 - access("S"."CHANNEL"="C"."CHANNEL_KEY")
  10 - access("S"."CUSTOMER"="CU"."CUSTOMER_KEY")

28 Zeilen ausgewählt.

SQL>
SQL> SELECT SUM(s.quantity)      AS quantity
  2  ,      SUM(s.sales)         AS sales
  3  ,      t.calendar_year_name AS cy
  4  ,      p.department_name    AS dept
  5  ,      c.class_name         AS class
  6  ,      cu.country_name      AS country
  7  FROM  times      t
  8  ,     products   p
  9  ,     channels   c
 10  ,     customers  cu
 11  ,     sales_fact s
 12  WHERE p.item_key = s.product
 13  AND   s.day_key  = t.day_key
 14  AND   s.channel  = c.channel_key
 15  AND   s.customer = cu.customer_key
 16  GROUP BY p.department_name
 17  ,        t.calendar_year_name
 18  ,        c.class_name
 19  ,        cu.country_name;

    QUANTITY            SALES CY         DEPT                           CLASS                          COUNTRY
------------ ---------------- ---------- ------------------------------ ------------------------------ ----------------
   9,674.000      762,122.880 CY2005     Cameras and Camcorders         Indirect                       India
   3,298.000      265,330.830 CY2005     Cameras and Camcorders         Direct                         France
   1,282.000      106,553.330 CY2005     Cameras and Camcorders         Indirect                       Japan
   1,981.000      331,317.060 CY2005     Computers                      Indirect                       Thailand
     192.000       13,417.360 CY2005     Cameras and Camcorders         Indirect                       Greece
.
.
.
  16,923.000    2,629,690.800 CY2005     Computers                      Indirect                       Mexico
  13,031.000    2,214,411.850 CY2006     Computers                      Indirect                       Indonesia
  19,605.000    3,259,527.220 CY2006     Computers                      Direct                         India
   9,297.000    1,445,947.580 CY2005     Computers                      Direct                         Brazil
  15,325.000    1,354,299.540 CY2007     Cameras and Camcorders         Direct                         United States

1152 Zeilen ausgewählt.

Abgelaufen: 00:00:34.76
SQL>
Im dritten Beispiel wird lediglich die Gruppierung geändert, indem nicht die Verdichtung je Land gewählt wird, sondern die Verdichtung nach Regionen. Die Verknüpfung zwischen den Dimensions- und der Faktentabelle findet jeweils über den entsprechenden Fremdschlüssel statt. 
SQL> -- 3. Quantity and Sales by Year, Category, Class and Region
SQL> COLUMN region   FORMAT a30
SQL> EXPLAIN PLAN FOR
  2  SELECT SUM(s.quantity)      AS quantity
  3  ,      SUM(s.sales)         AS sales
  4  ,      t.calendar_year_name AS cy
  5  ,      p.category_name      AS cat
  6  ,      c.class_name         AS class
  7  ,      cu.region_name       AS region
  8  FROM  times      t
  9  ,     products   p
 10  ,     channels   c
 11  ,     customers  cu
 12  ,     sales_fact s
 13  WHERE p.item_key = s.product
 14  AND   s.day_key = t.day_key
 15  AND   s.channel = c.channel_key
 16  AND   s.customer = cu.customer_key
 17  GROUP BY p.category_name
 18  ,        t.calendar_year_name
 19  ,        c.class_name
 20  ,        cu.region_name;

EXPLAIN PLAN ausgeführt.

SQL>
SQL> @c:\oracle\product\11.1.0\rdbms\admin\utlxplp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3596963109

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |   136 | 13192 |       | 14762   (2)| 00:02:58 |       |       |
|   1 |  HASH GROUP BY                   |            |   136 | 13192 |       | 14762   (2)| 00:02:58 |       |       |
|*  2 |   HASH JOIN                      |            |  2811K|   260M|       | 14623   (1)| 00:02:56 |       |       |
|   3 |    TABLE ACCESS FULL             | PRODUCTS   |  2713 | 81390 |       |    40   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |            |  2811K|   179M|       | 14568   (1)| 00:02:55 |       |       |
|   5 |     PART JOIN FILTER CREATE      | :BF0000    |  1461 | 21915 |       |    66   (0)| 00:00:01 |       |       |
|   6 |      PARTITION RANGE ALL         |            |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|   7 |       TABLE ACCESS FULL          | TIMES      |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|*  8 |     HASH JOIN                    |            |  2811K|   139M|       | 14487   (1)| 00:02:54 |       |       |
|   9 |      TABLE ACCESS FULL           | CHANNELS   |    13 |   143 |       |     3   (0)| 00:00:01 |       |       |
|* 10 |      HASH JOIN                   |            |  2811K|   109M|  6432K| 14469   (1)| 00:02:54 |       |       |
|  11 |       TABLE ACCESS FULL          | CUSTOMERS  |   253K|  3460K|       |  5015   (1)| 00:01:01 |       |       |
|  12 |       PARTITION RANGE JOIN-FILTER|            |  2811K|    72M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
|  13 |        TABLE ACCESS FULL         | SALES_FACT |  2811K|    72M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."ITEM_KEY"="S"."PRODUCT")
   4 - access("S"."DAY_KEY"="T"."DAY_KEY")
   8 - access("S"."CHANNEL"="C"."CHANNEL_KEY")
  10 - access("S"."CUSTOMER"="CU"."CUSTOMER_KEY")

28 Zeilen ausgewählt.

SQL>
SQL> SELECT SUM(s.quantity)      AS quantity
  2  ,      SUM(s.sales)         AS sales
  3  ,      t.calendar_year_name AS cy
  4  ,      p.category_name      AS cat
  5  ,      c.class_name         AS class
  6  ,      cu.region_name       AS region
  7  FROM  times      t
  8  ,     products   p
  9  ,     channels   c
 10  ,     customers  cu
 11  ,     sales_fact s
 12  WHERE p.item_key = s.product
 13  AND   s.day_key = t.day_key
 14  AND   s.channel = c.channel_key
 15  AND   s.customer = cu.customer_key
 16  GROUP BY p.category_name
 17  ,        t.calendar_year_name
 18  ,        c.class_name
 19  ,        cu.region_name;

    QUANTITY            SALES CY       CAT                            CLASS      REGION
------------ ---------------- -------- ------------------------------ ---------- ---------------
  78,071.000   11,689,230.670 CY2005   Total Personal Computers       Direct     Asia
  11,261.000    1,253,452.700 CY2005   Total iPlayer Family           Indirect   South America
   9,508.000      522,016.800 CY2005   Camcorders and Accessories     Indirect   North America
   3,791.000      415,282.350 CY2005   Total iPlayer Family           Direct     South America
   3,031.000      519,360.970 CY2005   Computer Printers and Supplies Indirect   Africa
.
.
.
  46,653.000    5,247,206.730 CY2007   Total iPlayer Family           Indirect   Asia
  23,242.000    1,317,191.600 CY2006   Camcorders and Accessories     Indirect   Asia
       1.000          279.000 CY2007   Total Server Computers         Direct     Oceania
  11,001.000    1,375,352.400 CY2005   Cameras and Accessories        Indirect   Asia
   7,564.000    1,075,606.520 CY2007   Cameras and Accessories        Direct     Asia

275 Zeilen ausgewählt.

Abgelaufen: 00:00:28.25
SQL>
Im letzten Beispiel wird eine Verdichtung nach Jahr, Department und Region durchgeführt, die Dimension CHANNEL wird nicht berücksichtigt. Auch hier wird das Ergebnis durch den Oracle Optimizer über Full Table Scans, Hash-Joins und die partitionsweise Verknüpfung ermittelt. 
SQL> -- 4. Quantity & Sales by Year, Department, and Region
SQL> EXPLAIN PLAN FOR
  2  SELECT  SUM(s.quantity)      AS quantity
  3  ,       SUM(s.sales)         AS sales
  4  ,       t.calendar_year_name AS cy
  5  ,       p.department_name    AS dept
  6  ,       cu.region_name       AS region
  7  FROM  times      t
  8  ,     customers  cu
  9  ,     products   p
 10  ,     sales_fact s
 11  WHERE(cu.customer_key = s.customer
 12  AND   p.item_key      = s.product
 13  AND   s.day_key       = t.day_key)
 14  GROUP BY cu.region_name
 15  ,        p.department_name
 16  ,        t.calendar_year_name;

EXPLAIN PLAN ausgeführt.

SQL>
SQL> @c:\oracle\product\11.1.0\rdbms\admin\utlxplp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1215509445

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |    37 |  2590 |       | 14346   (2)| 00:02:53 |       |       |
|   1 |  HASH GROUP BY                   |            |    37 |  2590 |       | 14346   (2)| 00:02:53 |       |       |
|*  2 |   HASH JOIN                      |            |  2811K|   187M|       | 14207   (1)| 00:02:51 |       |       |
|   3 |    TABLE ACCESS FULL             | PRODUCTS   |  2713 | 46121 |       |    40   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |            |  2811K|   142M|       | 14152   (1)| 00:02:50 |       |       |
|   5 |     PART JOIN FILTER CREATE      | :BF0000    |  1461 | 21915 |       |    66   (0)| 00:00:01 |       |       |
|   6 |      PARTITION RANGE ALL         |            |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|   7 |       TABLE ACCESS FULL          | TIMES      |  1461 | 21915 |       |    66   (0)| 00:00:01 |     1 |1048575|
|*  8 |     HASH JOIN                    |            |  2811K|   101M|  6432K| 14071   (1)| 00:02:49 |       |       |
|   9 |      TABLE ACCESS FULL           | CUSTOMERS  |   253K|  3460K|       |  5015   (1)| 00:01:01 |       |       |
|  10 |      PARTITION RANGE JOIN-FILTER |            |  2811K|    64M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
|  11 |       TABLE ACCESS FULL          | SALES_FACT |  2811K|    64M|       |  3928   (2)| 00:00:48 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."ITEM_KEY"="S"."PRODUCT")
   4 - access("S"."DAY_KEY"="T"."DAY_KEY")
   8 - access("CU"."CUSTOMER_KEY"="S"."CUSTOMER")

25 Zeilen ausgewählt.

SQL>
SQL> SELECT  SUM(s.quantity)      AS quantity
  2  ,       SUM(s.sales)         AS sales
  3  ,       t.calendar_year_name AS cy
  4  ,       p.department_name    AS dept
  5  ,       cu.region_name       AS region
  6  FROM  times      t
  7  ,     customers  cu
  8  ,     products   p
  9  ,     sales_fact s
 10  WHERE(cu.customer_key = s.customer
 11  AND   p.item_key      = s.product
 12  AND   s.day_key       = t.day_key)
 13  GROUP BY cu.region_name
 14  ,        p.department_name
 15  ,        t.calendar_year_name;

    QUANTITY            SALES CY         DEPT                           REGION
------------ ---------------- ---------- ------------------------------ ------------------------
   4,837.000      392,434.980 CY2005     Cameras and Camcorders         Africa
   5,871.000      647,626.590 CY2005     Portable Music and Video       Africa
  81,810.000   13,999,990.170 CY2007     Computers                      South America
  64,765.000    7,238,704.770 CY2006     Portable Music and Video       Asia
  70,405.000    7,905,262.260 CY2007     Portable Music and Video       Asia
.
.
.
  15,966.000    1,777,808.490 CY2006     Portable Music and Video       South America
     441.000       67,279.520 CY2005     Computers                      Oceania
      71.000        9,684.960 CY2005     Cameras and Camcorders         Oceania
  59,795.000    6,704,484.880 CY2005     Portable Music and Video       Asia
  18,527.000    1,578,657.120 CY2006     Cameras and Camcorders         Europe

54 Zeilen ausgewählt.

Abgelaufen: 00:00:24.12
SQL>

Ausführen von Statements mit Query Rewrite

Um Query Rewrite nutzen zu können, muss die Einstellung des Optimizers entsprechend gewählt sein. Da eine Materialized View nicht grundsätzlich bei jeder Veränderung der Basistabellen aktualisiert wird bzw. werden kann, ist es häufig sinnvoll, den Parameter QUERY_REWRITE_INTEGRITY auf STALE_TOLERATED zu stellen. Dies bewirkt, dass ein Rewrite auch dann erfolgen kann, wenn die Materialized View "STALE" ist, also nicht auf dem aktuellen Stand ist. Bei Datawarehouses, die täglich in einem nächtlichen Ladeprozeß gefüllt werden, ist diese Einstellung oft nicht relevant, da Ladeläufe und Auswertungen zeitlich vonenander entkoppelt sind. In anderen Szenarien, etwa wenn operationale Daten ausgewertet werden, die auch im laufenden Betrieb aktualisiert werden müssen, ist es wichtig, nur dann ein Query Rewrite zu erlauben, wenn sicher ist, dass zwischen Materialized View und Basistabellen keine Inkonsistenzen bestehen.

Das Query Rewrite kann auch erzwungen werden, indem der Parameter QUERY_REWRITE_ENABLED auf FORCE gesetzt wird. In diesem Fall wird grundsätzlich die Materialized View herangezogen, es sei denn, die STALENESS hat den Status UNUSABLE. Da der Ausführungsplan in jedem der Beispiele fast identisch aussieht, wird hier lediglich der erste gezeigt, danach nur noch die Zeiten der Ausführung. 
SQL> -- 2nd run: QUERY REWRITE ENABLED
SQL> ALTER SESSION SET query_rewrite_integrity = stale_tolerated;

Session wurde geändert.

SQL> ALTER SESSION SET query_rewrite_enabled   = force;

Session wurde geändert.

SQL> ALTER MATERIALIZED VIEW cb$sales_cube ENABLE QUERY REWRITE;

Materialized View wurde geändert.

SQL>
SQL> -- 1. Quantity and Sales by Year and Product Category
SQL> COLUMN quantity FORMAT 999,990.000
SQL> COLUMN sales    FORMAT 999,999,990.000
SQL> COLUMN cy       FORMAT a10
SQL> COLUMN cat      FORMAT a35
SQL> EXPLAIN PLAN FOR
  2  SELECT SUM(s.quantity)      AS quantity
  3  ,      SUM(s.sales)         AS sales
  4  ,      t.calendar_year_name AS cy
  5  ,      p.category_name      AS cat
  6  FROM  times      t
  7  ,     products   p
  8  ,     sales_fact s
  9  WHERE p.item_key = s.product
 10  AND   s.day_key  = t.day_key
 11  GROUP BY p.category_name
 12  ,        t.calendar_year_name;

EXPLAIN PLAN ausgeführt.

SQL>
SQL> @c:\oracle\product\11.1.0\rdbms\admin\utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 826791475

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |  2859 |   226K|    12   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                 |               |  2859 |   226K|    12   (9)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE CUBE ACCESS | CB$SALES_CUBE |  2859 |   226K|    11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYS_OP_ATG(VALUE(KOKBF$),55,56,2)=533112783359)

14 Zeilen ausgewählt.

SQL>
SQL> SELECT SUM(s.quantity)      AS quantity
  2  ,      SUM(s.sales)         AS sales
  3  ,      t.calendar_year_name AS cy
  4  ,      p.category_name      AS cat
  5  FROM  times      t
  6  ,     products   p
  7  ,     sales_fact s
  8  WHERE p.item_key = s.product
  9  AND   s.day_key  = t.day_key
 10  GROUP BY p.category_name
 11  ,        t.calendar_year_name;

    QUANTITY            SALES CY         CAT
------------ ---------------- ---------- -----------------------------------
     156.000       62,522.000 CY2005     All Computer Furniture
 145,934.000   16,264,253.090 CY2006     Total iPlayer Family
  79,348.000    4,515,305.600 CY2006     Camcorders and Accessories
     411.000       93,239.610 CY2006     PDAs
  72,008.000    4,068,490.800 CY2005     Camcorders and Accessories
.
.
.
 135,179.000   15,102,590.700 CY2005     Total iPlayer Family
  91,317.000   15,663,215.050 CY2005     Computer Printers and Supplies
   4,846.000    1,938,230.600 CY2006     Total Server Computers
  99,579.000   18,339,588.070 CY2006     Computer Printers and Supplies
 570,861.000   91,680,172.610 CY2006     Total Personal Computers

24 Zeilen ausgewählt.

Abgelaufen: 00:00:00.28
SQL>
SQL> -- 2. Quantity and Sales by Year, Department, Class and Country
.
.
.
Abgelaufen: 00:00:04.43
SQL>
SQL> -- 3. Quantity and Sales by Year, Category, Class and Region
.
.
.
Abgelaufen: 00:00:00.92
SQL>
SQL> -- 4. Quantity & Sales by Year, Department, and Region
.
.
.
Abgelaufen: 00:00:00.56
SQL>

Fazit

Die abschließend durchgeführten Statements unterstreichen auf eindrucksvolle Weise, wie sich die Performance von Abfragen mit Hilfe Cube-organized Materialized Views und Query Rewrite steigern läßt. Zusammengefaßt in einer Aufstellung sprechen die Zahlen für sich:

 Dauer ohne Query RewriteDauer mit Query Rewrite
Statement 16,430,28
Statement 234,764,43
Statement 328,250,92
Statement 424,120,56


Natürlich sind die Ergebnisse umso beeindruckender, je höher die zu berechnende Verdichtung ist. In Fällen hingegen, in denen eine Star-Query Optimization greift, also auf sehr granulare Daten abgefragt wird, bringen die verdichteten Berechnungen in einem OLAP Cube keinen signifikanten Vorteil - für die gute Performance sorgt die Star-Query Optimization dann selbst. Auf diese Weise tragen die unterschiedlichen Technologien im Oracle Datawarehouse zu einer sehr guten Performance in nahezu allen Anwendungssituationen bei.

Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite