Beispiele für den Algorithmus für die Ansichtenübereinstimmung

Falls Abfragen häufig auf eine bestimmte Partition einer Basistabelle zugreifen, kann es vorteilhaft sein, eine materialisierte Ansicht zu definieren, um diese Partition zu speichern. Beispielsweise speichert die nachfolgend definierte materialisierte Ansicht "V_Canada" alle Kunden aus der Tabelle "Customer", die in Kanada wohnen. Da diese materialisierte Ansicht benutzt wird, wenn die Spalte "State" auf bestimmte Werte beschränkt wurde, ist es ratsam, den Index "V_Canada_State" für die Spalte "State" der materialisierten Ansicht "V_Canada" zu erstellen.

CREATE MATERIALIZED VIEW V_Canada AS
 SELECT c.ID, c.City, c.State, c.CompanyName
  FROM Customers c
  WHERE c.State IN ( 'AB', 'BC', 'MB', 'NB', 'NL', 
   'NT', 'NS', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT' );
REFRESH MATERIALIZED VIEW V_Canada;
CREATE INDEX V_Canada_State on V_Canada( State );

Diese materialisierte Ansicht kann Vorteile für alle Abfrageblöcke bieten, die nur eine Teilmenge der Kunden benötigen, die in Kanada wohnen. Beispiel: Die folgende Abfrage 1, die den Gesamtpreis der Produkte für alle Kunden in Ontario für jede einzelne Firma berechnet, könnte die materialisierte Ansicht "V_Canada" in ihren Zugriffsplänen benutzen. Ein Zugriffsplan für Abfrage 1, der die materialisierte Ansicht "V_Canada" benutzt, stellt einen gültigen Plan dar. Dies hat die gleiche Wirkung wie das Umschreiben der Abfrage 1 zu Abfrage 1_v, die semantisch gleichwertig mit Abfrage 1 ist. Beachten Sie, dass der Optimierer die Abfrage nicht unter Verwendung der materialisierten Ansichten umschreibt, sondern dass die generierten Zugriffspläne, welche die materialisierten Ansichten verwenden, theoretisch der umgeschriebenen Abfrage entsprechen.

Der Ausführungsplan der Abfrage 1 verwendet die materialisierte Ansicht "V_Canada" wie hier dargestellt: Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ]

Abfrage 1:

SELECT SUM( SalesOrderItems.Quantity
   * Products.UnitPrice ) AS Value
 FROM Customers c
  LEFT OUTER JOIN SalesOrders 
   ON( SalesOrders.CustomerID = c.ID )
  LEFT OUTER JOIN SalesOrderItems 
   ON( SalesOrderItems.ID = SalesOrders.ID )
  LEFT OUTER JOIN Products 
   ON( Products.ID = SalesOrderItems.ProductID )
 WHERE c.State = 'ON'
 GROUP BY c.CompanyName;

Abfrage 1_v:

SELECT SUM( SalesOrderItems.Quantity
   * Products.UnitPrice ) AS Value
  FROM V_Canada 
   LEFT OUTER JOIN SalesOrders ON( SalesOrders.CustomerID = V_Canada.ID )
   LEFT OUTER JOIN SalesOrderItems ON( SalesOrderItems.ID = SalesOrders.ID )
   LEFT OUTER JOIN Products ON( Products.ID = SalesOrderItems.ProductID )
  WHERE V_Canada.State = 'ON'
  GROUP BY V_Canada.CompanyName;

Abfrage 2 kann diese Ansicht sowohl im Hauptabfrageblock als auch in der Unterabfrage HAVING verwenden. Einige der vom Optimierer aufgelisteten Zugriffspläne, welche die materialisierte Ansicht "V_Canada" verwenden, stellen die Abfrage 2_v dar, die der Abfrage 2 semantisch gleichwertig ist, in der die Tabelle "Customer" durch die Ansicht "V_Canada" ersetzt wurde.

Der Ausführungsplan lautet folgendermaßen: Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ] : GrByS[ V_Canada<seq> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey>

Abfrage 2:

SELECT  SUM( SalesOrderItems.Quantity
  * Products.UnitPrice ) AS Value
 FROM Customers c
  LEFT OUTER JOIN SalesOrders 
   ON( SalesOrders.CustomerID = c.ID )
  LEFT OUTER JOIN SalesOrderItems 
   ON( SalesOrderItems.ID = SalesOrders.ID )
  LEFT OUTER JOIN Products 
   ON( Products.ID = SalesOrderItems.ProductID )
 WHERE c.State = 'ON'
 GROUP BY CompanyName
 HAVING Value > 
  ( SELECT AVG( SalesOrderItems.Quantity
        * Products.UnitPrice ) AS Value
      FROM Customers c1
       LEFT OUTER JOIN SalesOrders 
       ON( SalesOrders.CustomerID = c1.ID )
       LEFT OUTER JOIN SalesOrderItems 
       ON( SalesOrderItems.ID = SalesOrders.ID )
        LEFT OUTER JOIN Products 
       ON( Products.ID = SalesOrderItems.ProductID )
       WHERE c1.State IN ('AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 
      'NU', 'ON', 'PE', 'QC', 'SK', 'YT' ) );

Abfrage 2_v:

SELECT  SUM( SalesOrderItems.Quantity
         * Products.UnitPrice ) AS Value
 FROM V_Canada
 LEFT OUTER JOIN SalesOrders 
  ON( SalesOrders.CustomerID=V_Canada.ID )
 LEFT OUTER JOIN SalesOrderItems 
  ON( SalesOrderItems.ID=SalesOrders.ID )
 LEFT OUTER JOIN Products 
  ON( Products.ID=SalesOrderItems.ProductID )
WHERE V_Canada.State = 'ON'
GROUP BY V_Canada.CompanyName
HAVING Value > 
   ( SELECT AVG( SalesOrderItems.Quantity
         * Products.UnitPrice ) AS Value
       FROM V_Canada
        LEFT OUTER JOIN SalesOrders 
        ON( SalesOrders.CustomerID = V_Canada.ID )
        LEFT OUTER JOIN SalesOrderItems 
        ON( SalesOrderItems.ID = SalesOrders.ID )
         LEFT OUTER JOIN Products 
        ON( Products.ID = SalesOrderItems.ProductID )
        WHERE V_Canada.State IN ('AB', 'BC', 'MB', 
       'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 'PE', 'QC', 
       'SK', 'YT' ) );

Die gruppierten materialisierten Ansichten haben die höchsten Auswirkungen auf die Performance gruppierter Abfragen. Falls ähnliche Aggregationen in häufig ausgeführten Abfragen benutzt werden, sollte eine materialisierte Ansicht definiert werden, um die Daten für eine Obermenge der in diesen Abfragen benutzten GROUP BY-Klauseln vorher anzusammeln. Zusammengesetzte Aggregatfunktionen von Abfragen können von einfachen, in den Ansichten verwendeten Aggregaten berechnet werden. Daher wird empfohlen, dass nur einfache Aggregatfunktionen in materialisierten Ansichten gespeichert werden.

Die unten stehende materialisierte Ansicht "V_Quantity" führt eine Vorberechnung der Summen und der Anzahlen pro Produkt für die einzelnen Monate und Jahre durch. Die folgende Abfrage 3 kann diese Ansicht benutzen, um nur die Monate des Jahres 2000 auszuwählen (der kurze Plan ist Work[ GrByH[ V_Quantity<seq> ] ] entsprechend Abfrage 3_v).

Abfrage 4, die keine Referenz auf die Erweiterungstabelle "SalesOrders" enthält, kann trotzdem V_Quantity verwenden, da die Ansicht alle erforderlichen Daten zum Berechnen von Abfrage 4 enthält (der kurze Plan ist Work[ GrByH[ V_Quantity<seq> ] ] entsprechend Abfrage 4_v).

CREATE MATERIALIZED VIEW V_Quantity AS
 SELECT s.ProductID, 
  Month( o.OrderDate ) AS month,
  Year( o.OrderDate ) AS year, 
  SUM( s.Quantity ) AS q_sum,
  COUNT( s.Quantity  ) AS q_count       
 FROM SalesOrderItems s KEY JOIN SalesOrders o
 GROUP BY s.ProductID, Month( o.OrderDate ), 
  Year( o.OrderDate );
REFRESH MATERIALIZED VIEW V_Quantity;

Abfrage 3:

SELECT s.ProductID, 
  Month( o.OrderDate ) AS month,
  AVG( s.Quantity) AS avg, 
  SUM( s.Quantity ) AS q_sum,
  COUNT( s.Quantity ) AS q_count       
 FROM SalesOrderItems s KEY JOIN SalesOrders o 
 WHERE year( o.OrderDate ) = 2000
 GROUP BY s.ProductID, Month( o.OrderDate );

Abfrage 3_v:

SELECT V_Quantity.ProductID, 
  V_Quantity.month AS month,
  SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
   AS avg, 
  SUM( V_Quantity.q_sum ) AS q_sum,
  SUM( V_Quantity.q_count ) AS q_count       
 FROM V_Quantity
 WHERE V_Quantity.year  = 2000
 GROUP BY V_Quantity.ProductID, V_Quantity.month;

Abfrage 4:

SELECT s.ProductID, 
  AVG( s.Quantity ) AS avg,
  SUM( s.Quantity ) AS sum 
 FROM SalesOrderItems s  
 WHERE s.ProductID IS NOT NULL
 GROUP BY s.ProductID;

Abfrage 4_v

SELECT V_Quantity.ProductID, 
  SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
   AS avg, 
  SUM( V_Quantity.q_sum ) AS sum  
 FROM V_Quantity
 WHERE V_Quantity.ProductID IS NOT NULL
 GROUP BY V_Quantity.ProductID;

Der Algorithmus für die Ansichtenübereinstimmung wird pro Abfrageblock angewendet. Daher ist es möglich, mehr als eine materialisierte Ansicht je Abfrageblock zu verwenden, und ebenso mehr als eine materialisierte Ansicht für die komplette Abfrage. Die nachfolgende Abfrage 5 kann die drei materialisierten Ansichten verwenden: V_Canada für eine der Nullwert-liefernden Seiten von LEFT OUTER JOIN, V_ShipDate, die weiter unten definiert wird, für die bewahrte Seite des Hauptabfrageblocks und V_Quantity für den Unterabfrageblock. Der Ausführungsplan für die Abfrage 5_v lautet: Work[ Window[ Sort[ V_ShipDate<V_ShipDate_date> JNLO ( so<SalesOrdersKey> JH V_Canada<V_Canada_State> ) ] ] ] : GrByS[V_Quantity<seq> ].

CREATE MATERIALIZED VIEW V_ShipDate AS
 SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s KEY JOIN Products p ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01';
REFRESH MATERIALIZED VIEW V_ShipDate;
CREATE INDEX V_ShipDate_date ON V_ShipDate( ShipDate );

Abfrage 5:

SELECT p.ID, p.Description, s.Quantity, 
  s.ShipDate, so.CustomerID, c.CompanyName,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s JOIN Products p 
  ON (s.ProductID = p.ID) LEFT OUTER JOIN ( 
   SalesOrders so JOIN Customers c 
    ON ( c.ID = so.CustomerID AND c.State = 'ON' ) )
  ON (s.ID = so.ID )
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2000-12-31' 
   AND s.Quantity  > ( SELECT  AVG( s.Quantity ) AS avg
                        FROM SalesOrderItems s KEY JOIN SalesOrders o
                        WHERE year( o.OrderDate) = 2000 )
 FOR READ ONLY;

Abfrage 5_v:

SELECT  V_ShipDate.ID, V_ShipDate.Description, 
  V_ShipDate.Quantity, V_ShipDate.ShipDate, 
  so.CustomerID, V_Canada.CompanyName,
  SUM( V_ShipDate.Quantity ) OVER ( PARTITION BY V_ShipDate.ProductID
                                     ORDER BY V_ShipDate.ShipDate
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND CURRENT ROW ) AS cumulative_qty
 FROM V_ShipDate
  LEFT OUTER JOIN ( SalesOrders so JOIN V_Canada
   ON ( V_Canada.ID = so.CustomerID AND V_Canada.State = 'ON' ) )
  ON ( V_ShipDate.ID = so.ID )
 WHERE V_ShipDate.ShipDate >= '2000-01-01' 
  AND V_ShipDate.ShipDate <= '2000-12-31' 
  AND V_ShipDate.Quantity >
    ( SELECT SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
       FROM V_Quantity
       WHERE V_Quantity.year = 2000 )
 FOR READ ONLY;

Der Algorithmus für die Ansichtenübereinstimmung kann Ansichten und Abfragen mit OUTER JOINs abgleichen, indem ähnliche Regeln wie für Ansichten verwendet werden, die nur Inner-Joins enthalten. Nullwert-liefernde Seiten der OUTER JOINs einer materialisierten Ansicht werden in der Abfrage möglicherweise nur dann angezeigt, wenn alle Tabellen in der Nullwert-liefernden Seite Erweiterungstabellen sind. Die Abfrage kann Inner-Joins enthalten, um den Outer-Joins der Ansicht zu entsprechen. Die unten aufgeführten Abfragen 6_v, 7_v, 8_v und 9_v zeigen, wie eine materialisierte Ansicht, die einen OUTER JOIN in ihrer Definition enthält, zur Beantwortung von Abfragen verwendet werden kann.

Abfrage 6 stimmt genau mit der materialisierten Ansicht V_SalesOrderItems_2000 überein und kann genauso wie Abfrage 6_v bewertet werden.

Abfrage 7 enthält einige zusätzliche Prädikate für die bewahrte Seite des Outer-Joins und kann weiterhin mit V_SalesOrderItems_2000 berechnet werden. Beachten Sie, dass die Nullwert-liefernde Tabelle "Products" eine Erweiterungstabelle in der Ansicht V_SalesOrderItems_2000 ist. Dies bedeutet, dass die Ansicht auch mit Abfrage 8 abgeglichen werden kann, die nicht die Tabelle "Products" enthält.

Abfrage 9 enthält nur den Inner-Join der Tabellen "SalesOrderItems" und "Products" und wird mit der Ansicht V_SalesOrderItems_2000 abgeglichen. Hierfür werden nur die Zeilen der Ansicht ausgewählt, die keine Nullwert-liefernden Zeilen aus der Tabelle "Products" sind. Das zusätzliche Prädikat V.Description IS NOT NULL in Abfrage 9_v wird dazu verwendet, nur die nicht Nullwert-liefernden Zeilen auszuwählen.

CREATE MATERIALIZED VIEW V_SalesOrderItems_2000 AS
 SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01';
REFRESH MATERIALIZED VIEW V_SalesOrderItems_2000;
CREATE INDEX V_SalesOrderItems_shipdate ON V_SalesOrderItems_2000( ShipDate );

Abfrage 6:

SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01'
 FOR READ ONLY;

Abfrage 6_v:

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V     
 FOR READ ONLY;

Abfrage 7:

SELECT s.ProductID,  p.Description, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON (s.ProductID = p.ID )
      
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01'  
   AND s.Quantity >= 50  
 FOR READ ONLY;

Abfrage 7_v:

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Quantity >= 50  
 FOR READ ONLY;

Abfrage 8:

SELECT s.ProductID, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s  
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01' 
   AND s.Quantity >= 50   
 FOR READ ONLY;

Abfrage 8_v:

SELECT V.ProductID, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Quantity >= 50  
 FOR READ ONLY;

Abfrage 9:

SELECT s.ProductID,  p.Description, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s JOIN Products p 
      ON (s.ProductID = p.ID )
      
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01'   
 FOR READ ONLY;

Abfrage 9_v:

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Description IS NOT NULL   
 FOR READ ONLY;

Beispiele für den Algorithmus für die Ansichtenübereinstimmung

Beispiel 1: Übereinstimmende "select-project-join"-Ansichten

Beispiel 2: Übereinstimmende "grouped-select-project-join"-Ansichten

Beispiel 3: Übereinstimmende komplexe Abfragen

Beispiel 4: Übereinstimmende materialisierte Ansichten mit OUTER-JOINs