使用用户维护的具体化查询表优化查询执行

查看以下示例以了解如何使用用户维护的具体化查询表 (MQT) 优化查询执行。

MQT 可以降低成本高昂的基本数据连接、排序和聚集需求。 尽管 MQT 中未包括查询的准确答案,但使用 MQT 计算答案的成本可能显著低于使用大型基本表的成本,这是因为此答案的其中一部分已计算完毕。

优化多维分析查询的示例

场景描述

假定数据库仓库包含一组客户和一组信用卡帐户。 仓库记录使用信用卡进行的交易。 每项交易都包含一批一起购买的商品。

此模式分类为多星型模式,这是因为它共有两个大型表,一个包含交易商品,另一个标识采购交易。 以下分层维度描述交易:
  • 产品层次结构存储在分别表示产品组和产品系列的两个标准表中。
  • 位置层次结构包含城市、州或省以及国家或地区信息,并且存储在单个非标准表中。
  • 时间层次结构包含日、月和年信息,并且在单一日期字段中进行编码。 日期维度是使用内置函数从交易的日期字段中抽取的。

此模式中的其他表表示客户的帐户信息以及客户信息。

在下列层次结构的每一层创建用于存储销售信息的用户维护 MQT:
  • 产品
  • 位置
  • 时间(由年、月和日组成)
用户维护的 MQT 的示例
您可以创建用户维护的 MQT 以通过存储汇总数据满足很多查询。 以下 CREATE TABLE 语句说明如何创建一个 MQT,该 MQT 按照产品组和产品系列维度、按照城市、省/直辖市/自治区和国家或地区维度以及按照时间维来计算销售金额和销售件数。 在它的 GROUP BY 子句中,还包括多个其他的列。
   create table dba.pg_salessum
     as (
       select l.id as prodline, pg.id as pgroup,
         loc.country, loc.state, loc.city,
         l.name as linename, pg.name as pgname,
         year(pdate) as year, month(pdate) as month,
         t.status,
         sum(ti.amount) as amount,
         count(*) as count
       from cube.transitem as ti, cube.trans as t,
         cube.loc as loc, cube.pgroup as pg, cube.prodline as l
       where
         ti.transid = t.id and
         ti.pgid = pg.id and
         pg.lineid = l.id and
         t.locid = loc.id and
         year(pdate) > 1990
       group by l.id, pg.id, loc.country, loc.state, loc.city,
         year(pdate), month(pdate), t.status, l.name, pg.name
     )
   data initially deferred refresh deferred;

   refresh table dba.pg_salessum;
以下查询可利用 dba.pg_salessum MQT 中预先计算的值:
  • 按月和产品组的销售额
  • 1990 年以来的总销售额
  • 1995 年或 1996 年的销售额
  • 特定产品组或产品系列的销售总额
  • 1995 年和 1996 年特定产品组或产品系列的销售总额
  • 特定国家或地区的销售总额
返回 1995 和 1996 年销售总额的查询示例:
以下查询因使用 dba.pg_salessum MQT 中的汇总数据而获得大幅度性能提高。
   set current refresh age=any

   select year(pdate) as year, sum(ti.amount) as amount
     from cube.transitem as ti, cube.trans as t,
       cube.loc as loc, cube.pgroup as pg, cube.prodline as l
     where
       ti.transid = t.id and
       ti.pgid = pg.id and
       pg.lineid = l.id and
       t.locid = loc.id and
       year(pdate) in (1995, 1996)
     group by year(pdate);
以下查询示例按产品组返回 1995 和 1996 年销售总额:
以下查询还会受益于使用 dba.pg_salessum MQT 中的汇总数据。
   set current refresh age=any

   select pg.id as "PRODUCT GROUP", sum(ti.amount) as amount
     from cube.transitem as ti, cube.trans as t,
       cube.loc as loc, cube.pgroup as pg, cube.prodline as l
     where
       ti.transid = t.id and
       ti.pgid = pg.id and
       pg.lineid = l.id and
       t.locid = loc.id and
       year(pdate) in (1995, 1996)
     group by pg.id;