ビュー・マッチング・アルゴリズムの例

例 1：プロジェクト選択ジョイン・ビューの一致

ベース・テーブルの特定部分がクエリによって頻繁にアクセスされる場合、その部分を格納するマテリアライズド・ビューを定義すると便利な場合があります。たとえば、次のように定義されたマテリアライズド・ビュー V_Canada は、Customer テーブルからのカナダに住んでいるすべての顧客を格納します。このマテリアライズド・ビューは State カラムが特定の値に制限されるときに使用できるため、V_Canada マテリアライズド・ビューの State カラムでインデックス V_Canada_State を作成することをおすすめします。

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 );

カナダに住んでいる顧客のサブセットだけを必要とする任意のクエリ・ブロックは、このマテリアライズド・ビューの恩恵を受ける可能性があります。たとえばオンタリオ在住のすべての顧客に対して製品の合計額を会社ごとに計算する以下のクエリ 1 では、そのアクセス・プランで V_Canada マテリアライズド・ビューを使用する可能性があります。クエリ 1 がセマンティック上同等なクエリ 1_v に書き換えられたかのように、V_Canada マテリアライズド・ビューを使用するクエリ 1 のアクセス・プランは、有効なアクセス・プランを表します。オプティマイザは、マテリアライズド・ビューを使用するクエリを書き換えるのではなく、マテリアライズド・ビューを使用するアクセス・プランを生成するということに注意してください。このアクセス・プランは理論上、書き換えられたクエリに対応すると見なすことができます。

クエリ 1 の実行プランは Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ] で、V_Canada マテリアライズド・ビューを使用します。

クエリ 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;

クエリ 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;

クエリ 2 は、このビューをメイン・クエリ・ブロックと HAVING サブクエリの両方で使用します。オプティマイザが V_Canada マテリアライズド・ビューを使用して列挙するアクセス・プランの一部は、クエリ 2_v を表します。これはセマンティック上、Customer テーブルが V_Canada ビューで置き換えられたクエリ 2 と同一です。

実行プランは 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> です。

クエリ 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' ) );

クエリ 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' ) );

例 2：グループ化されたプロジェクト選択ジョイン・ビューの一致

グループ化されたマテリアライズド・ビューは、グループ化されたクエリに与えるパフォーマンスの影響がもっとも高い可能性があります。頻繁に実行されるクエリで類似した集約が使用される場合、これらのクエリで使用される GROUP BY 句のスーパーセットの集約前データに対してマテリアライズド・ビューが定義されなければなりません。クエリの複合集合関数は、ビューで使用される単純な集約から計算できます。そのため、マテリアライズド・ビューには単純な集合関数だけを格納することをおすすめします。

以下のマテリアライズド・ビュー V_quantity は、毎月と毎年の製品ごとの数量の合計とカウントを事前に計算します。以下のクエリ 3 ではこのビューを使用して、2000 年の月だけを選択できます (短いプランは Work[ GrByH[ V_quantity<seq> ] ] で、クエリ 3_v に対応する)。

クエリ 4 は拡張テーブル SalesOrders を参照しませんが、クエリ 4 の計算に必要なすべてのデータがビュー V_quantity に含まれているため、V_quantity を使用します (短いプランは Work[ GrByH[ V_quantity<seq> ] ] で、クエリ 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;

クエリ 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 );

クエリ 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;

クエリ 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;

クエリ 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;

例 3：複雑なクエリの一致

ビュー・マッチング・アルゴリズムは、クエリ・ブロックごとに適用されます。そのため、クエリ・ブロックごとに複数のマテリアライズド・ビューを使用できるため、クエリ全体で複数のマテリアライズド・ビューを使用できます。以下のクエリ 5 では、3 つのマテリアライズド・ビューを使用します。V_Canada は LEFT OUTER JOIN の NULL 入力側、V_ship_date はメイン・クエリ・ブロックの保護された側 (以下の定義参照)、V_quantity はサブクエリ・ブロックです。クエリ 5_v の実行プランは Work[ Window[ Sort[ V_ship_date<V_Ship_date_date> JNLO ( so<SalesOrdersKey> JH V_Canada<V_Canada_state> ) ] ] ] : GrByS[V_quantity<seq> ] です。

CREATE MATERIALIZED VIEW V_ship_date 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_ship_date;
CREATE INDEX V_ship_date_date ON V_ship_date( ShipDate );

クエリ 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;

クエリ 5_v：

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

例 4：マテリアライズド・ビューと外部ジョインの一致

ビュー・マッチング・アルゴリズムは、ビューと内部ジョインのみを含むクエリとの場合と同様のルールを使用して、ビューと外部ジョインを含むクエリとを対応させることができます。マテリアライズド・ビューで OUTER JOIN の NULL 入力側は、NULL 入力側のすべてのテーブルが拡張テーブルである場合は、表示されないことがあります。クエリには、ビューの外部ジョインと一致する内部ジョインを含めることが可能です。以下のクエリ 6_v、7_v、8_v、9_v では、定義に OUTER JOIN を含むマテリアライズド・ビューの別のクエリでの使用方法を示します。

以下のクエリ 6 は、マテリアライズド・ビュー V_SalesOrderItems_2000 と完全に一致し、これが 6_v であるかのように評価できます。

クエリ 7 では、外部ジョインの保護された側に述部が追加されていますが、V_SalesOrderItems_2000 を使用して計算できます。NULL 入力側のテーブル Products は、ビュー V_SalesOrderItems_2000 の拡張テーブルです。したがって、このビューは、Products テーブルが含まれないクエリ 8 とも一致します。

クエリ 9 には、テーブル SalesOrderItems と Products の内部ジョインだけが含まれます。V_SalesOrderItems_2000 ビューと一致するには、このビューでテーブル Products からの NULL 入力側ローではないローだけを選択します。クエリ 9_v での追加の述部 V.Description IS NOT NULL は、NULL 入力されないローだけを選択するために使用されます。

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 );

クエリ 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;

クエリ 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;

クエリ 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;

クエリ 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;

クエリ 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;

クエリ 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;

クエリ 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;

クエリ 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;