Szenario: Verbessern der Kardinalitätsschätzung durch Statistiksichten

Betrachten Sie die folgende Abfrage, die den Jahresumsatz für Golfschläger berechnet, die im Monat Juli jedes Jahres verkauft wurden:

   select  sum(f.sales_price), d2.year
     from  product d1, period d2, daily_sales f
     where  d1.prodkey = f.prodkey
       and  d2.perkey = f.perkey
       and  d1.item_desc = 'golf club'
       and  d2.month = 'JUL'
     group by  d2.year

Betrachten Sie eine Datenbank aus einem typischen Data-Warehouse, in dem STORE, CUSTOMER, PRODUCT, PROMOTION und PERIOD die Dimensionstabellen sind und DAILY_SALES die Fakttabelle ist. In den folgenden Tabellen sind die Definitionen für diese Tabellen aufgeführt.

Die Tabelle zeigt, dass Rabatte für wiederkehrende Kunden (Return customers) nur 2,86 % der 35 angebotenen Arten von Verkaufsförderungsmaßnahmen ausmachen.

Die folgende Abfrage gibt eine ermittelte Anzahl von 12.889.514 zurück:

   select  count(*)
     from  store d1, promotion d2, daily_sales f
     where  d1.storekey = f.storekey
       and  d2.promokey = f.promokey
       and  d1.store_number = '01'
       and  d2.promotype = 1

Diese Abfrage wird nach dem folgenden, vom Optimierungsprogramm generierten Plan ausgeführt. Bei jedem Knoten dieses Diagramms stellt die erste Zeile die Kardinalitätsschätzung, die zweite Zeile den Operatortyp und die dritte Zeile (Nummer in Klammern) die Operator-ID dar.

                                          6.15567e+06
                                             IXAND
                                             (   8)
                             /------------------+------------------\
                      2.15448e+07                                2.15448e+08
                        NLJOIN                                     NLJOIN
                        (   9)                                     (  13)
                 /---------+--------\                       /---------+--------\
               1                  2.15448e+07            18                1.19694e+07
            FETCH                   IXSCAN             FETCH                   IXSCAN
            (  10)                  (  12)             (  14)                  (  16)
           /---+---\                  |               /---+---\                  |
         35         35           7.54069e+08        18         63          7.54069e+08
      IXSCAN  TABLE: DB2DBA  INDEX: DB2DBA       IXSCAN   TABLE: DB2DBA       INDEX: DB2DBA
      (  11)       PROMOTION     PROMO_FK_IDX     (  15)         STORE             STORE_FK_IDX
        |                                          |
        35                                         63
  INDEX: DB2DBA                                INDEX: DB2DBA
 PROMOTION_PK_IDX                              STOREX1

Beim Join mit Verschachtelungsschleife (NLJOIN Nummer 9) schätzt das Optimierungsprogramm, dass rund 2,86 % des Produktverkaufs auf Kunden zurückzuführen ist, die zurückkommen, um die gleichen Produkte zu einem reduzierten Preis zu kaufen (2,15448e+07 ÷7,54069e+08 ≈ 0,0286). Beachten Sie, dass dies vor und nach dem Join der Tabelle PROMOTION mit der Tabelle DAILY_SALES der gleiche Wert ist. In Tabelle 8 sind die Kardinalitätsschätzungen und ihr Prozentsatz (Filtereffekt) vor und nach dem Join zusammengefasst.

Was ist die Ursache dafür, dass das Optimierungsprogramm die Anzahl der Datensätze, die die beiden Vergleichselemente erfüllen, nur auf die Hälfte der tatsächlichen Anzahl schätzt? Das Geschäft '01' repräsentiert ca. 28,57 % aller Geschäfte. Was wäre, wenn andere Geschäfte mehr Umsätze hätten als Geschäft '01' (weniger als 28,57 %)? Oder wenn das Geschäft '01' tatsächlich die meisten Produktumsätze erzielt hätte (mehr als 28,57 %)? Ebenso können die 2.86% der mit Werbeaktionstyp 1 verkauften Produkte (siehe Tabelle 8 ) irreführend sein. Der tatsächliche Prozentsatz in der Tabelle DAILY_SALES könnte sehr wohl ein anderer Wert sein als der projektierte.

Statistiksichten können dem Optimierungsprogramm helfen, solche Schätzwerte zu korrigieren. Zunächst müssen zwei Statistiksichten erstellt werden, welche die beiden Semi-Join-Verknüpfungen in der vorherigen Abfrage darstellen. Die erste Statistiksicht stellt die Verteilungsdaten von Geschäften für alle täglichen Umsätze bereit. Die zweite Statistiksicht stellt die Verteilung von Verkaufsförderungstypen für alle täglichen Umsätze dar. Beachten Sie, dass diese Statistiksichten die Verteilungsdaten für eine beliebige Geschäftsnummer bzw. einen beliebigen Verkaufsförderungstyp bereitstellen kann. In diesem Beispiel wird eine Stichprobenrate von 10 % verwendet, um die Datensätze aus der Tabelle DAILY_SALES für die jeweilige Sicht abzurufen und in globalen temporären Tabellen zu speichern. Anschließend werden diese Tabellen abgefragt, um die erforderlichen Statistikdaten zur Aktualisierung der beiden Statistiksichten zu sammeln.

1. Erstellen Sie eine Sicht, die den Join der Tabelle STORE mit der Tabelle DAILY_SALES darstellt.

      create view sv_store_dailysales as
        (select s.*
          from store s, daily_sales ds
          where s.storekey = ds.storekey)

2. Erstellen Sie eine Sicht, die den Join der Tabelle PROMOTION mit der Tabelle DAILY_SALES darstellt.

      create view sv_promotion_dailysales as
        (select p.*
          from promotion.p, daily_sales ds
          where p.promokey = ds.promokey)

3. Machen Sie die Sichten zu Statistiksichten, indem Sie sie für die Abfrageoptimierung aktivieren:

      alter view sv_store_dailysales enable query optimization
      alter view sv_promotion_dailysales enable query optimization

4. Führen Sie das Dienstprogramm RUNSTATS aus, um Statistikdaten für die Sichten zu erfassen:

      runstats on table db2dba.sv_store_dailysales with distribution
      runstats on table db2dba.sv_promotion_dailysales with distribution

5. Führen Sie die Abfrage erneut aus, damit sie erneut optimiert werden kann. Bei der Reoptimierung gleicht das Optimierungsprogramm die Sicht SV_STORE_DAILYSALES und die Sicht SV_PROMOTION_DAILYSALES mit der Abfrage ab und verwendet die Statistikdaten für die Sichten, um die Kardinalitätsschätzung der Semi-Join-Verknüpfungen zwischen der Fakttabelle und den Dimensionstabellen anzupassen. Dies führt zu einer Umkehrung der Reihenfolge der Semi-Join-Verknüpfungen, die ohne diese Statistiken ausgewählt wurden. Der neue Plan sieht folgendermaßen aus:

                                             1.04627e+07
                                               IXAND
                                               (   8)
                               /------------------+------------------\
                        6.99152e+07                                1.12845e+08
                          NLJOIN                                     NLJOIN
                          (   9)                                     (  13)
                   /---------+--------\                       /---------+--------\
                18                 3.88418e+06             1                  1.12845e+08
              FETCH                   IXSCAN             FETCH                   IXSCAN
              (  10)                  (  12)             (  14)                  (  16)
             /---+---\                  |               /---+---\                  |
          18           63           7.54069e+08      35           35          7.54069e+08
        IXSCAN   TABLE:DB2DBA     INDEX: DB2DBA   IXSCAN    TABLE: DB2DBA    INDEX: DB2DBA DB2DBA
       (  11)      STORE            STORE_FK_IDX    (  15)       PROMOTION        PROMO_FK_IDX
          |                                          |
          63                                         35
      INDEX: DB2DBA                              INDEX: DB2DBA
      STOREX1                                    PROMOTION_PK_IDX

In Tabelle 9 sind die Kardinalitätsschätzungen und ihr Prozentsatz (der Filtereffekt) vor und nach dem Join für jeden Semi-Join zusammengefasst.

Beachten Sie, dass jetzt die Semi-Join-Verknüpfung zwischen den Tabellen STORE und DAILY_SALES im äußeren Part des Plans zur logischen Verknüpfung der Indizes über AND ausgeführt wird. Dies liegt daran, dass die beiden Statistiksichten dem Optimierungsprogramm im Wesentlichen mitteilen, dass das Vergleichselement store_number = '01' mehr Zeilen filtert als promotype = 1. Dieses Mal schätzt das Optimierungsprogramm, dass ungefähr 10 462 700 Produkte verkauft werden. Diese Schätzung ist um den Faktor 1.23 (12 889 514 ÷ 10 462 700 ≈ 1.23) inaktiviert, was eine signifikante Verbesserung gegenüber der Schätzung ohne Statistiksichten (in Tabelle 8) darstellt.