Oracle Dimension 上 (ZT)

2006-02-28 16:22

在数据仓库环境中，我们通常利用物化视图强大的查询重写功能来提升统计查询的性能，但是物化视图的查询重写功能有时候无法智能地判断查询中一些相关联的条件，以至于影响性能。比如我们有一张销售表sales，用于存储订单的详细信息，包含交易日期、顾客编号和销售量。我们创建一张物化视图，按月存储累计销量信息，假如这时候我们要查询按季度或者按年度统计销量信息，Oracle是否能够智能地转换查询重写呢？我们知道交易日期中的日期意味着月，月意味着所处的季度，季度意味着年度，但是Oracle却是无法智能地判断这其中的关系，因此无法利用物化视图查询重写来返回我们季度或年度的销量信息，而是直接查询基表，导致性能产生问题。

这时候Dimension就派上用场了。Dimension用于说明列之间的父子对应关系，以使优化器能够自动转换不同列的关系，利用物化视图的查询功能来提升查询统计性能。下面我们首先创建一张销售交易表sales，包含交易日期、顾客编号和销售量这几个列，用于保存销售订单信息，整个表有42万多条记录；创建另一张表time_hierarchy用于存储交易日期中时间的关系，包含交易日期及其对应的月、季度及年度等信息，然后我们将体验Dimension的强大功能。

Roby@XUE> create table sales

2(trans_date date, cust_id int, sales_amount number );

Table created.

Roby@XUE> insert /*+ APPEND */ into sales

2select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,

3mod(rownum,100) CUST_ID,

4abs(dbms_random.random)/100 SALES_AMOUNT

5from all_objects

6/

5926 rows created.

Roby@XUE> commit;

Commit complete.

Roby@XUE> begin

2for i in 1 .. 6

3loop

4insert /*+ APPEND */ into sales

5select trans_date, cust_id, abs(dbms_random.random)/100 SALES_AMOUNT

6from sales;

7commit;

8end loop;

9end;

10/

PL/SQL procedure successfully completed.

Roby@XUE> select count(*) from sales;

COUNT(*)

----------

426672

创建索引组织表time_hierarchy，里面生成了交易日期中日期DAY、月MMYYYY、季度QTY_YYYY、年度YYYY的关系。

Roby@XUE> create table time_hierarchy

2(day primary key, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)

3organization index

4as

5select distinct

6trans_date DAY,

7cast (to_char(trans_date,'mmyyyy') as number) MMYYYY,

8to_char(trans_date,'mon-yyyy') MON_YYYY,

9'Q' || ceil( to_char(trans_date,'mm')/3) || ' FY'

10|| to_char(trans_date,'yyyy') QTR_YYYY,

11cast( to_char( trans_date, 'yyyy' ) as number ) YYYY

12from sales

13/

Table created.

接下我们创建一张物化视图mv_sales，用于存储每个客户对应每个月的销量统计信息。

Roby@XUE> create materialized view mv_sales

2build immediate

3refresh on demand

4enable query rewrite

5as

6select sales.cust_id, sum(sales.sales_amount) sales_amount,

7time_hierarchy.mmyyyy

8from sales, time_hierarchy

9where sales.trans_date = time_hierarchy.day

10group by sales.cust_id, time_hierarchy.mmyyyy

11/

Materialized view created.

我们对基表进行分析，以使优化器能够物化视图的查询重写功能：

Roby@XUE> analyze table sales compute statistics;

Table analyzed.

Roby@XUE> analyze table time_hierarchy compute statistics;

Table analyzed.

设置会话的查询重写功能：

Roby@XUE> alter session set query_rewrite_enabled=true;

Session altered.

Roby@XUE> alter session set query_rewrite_integrity=trusted;

Session altered.

接下来我们按月统计总的销量：

Roby@XUE> select time_hierarchy.mmyyyy, sum(sales_amount)

2from sales, time_hierarchy

3where sales.trans_date = time_hierarchy.day

4group by time_hierarchy.mmyyyy

5/

MMYYYY SUM(SALES_AMOUNT)

---------- -----------------

120064.0574E+11

120071.2297E+10

220063.6875E+11

320063.9507E+11

420063.7621E+11

520063.8549E+11

620063.6641E+11

720063.8110E+11

820063.8502E+11

920063.7278E+11

1020063.7983E+11

1120063.7210E+11

1220063.8364E+11

13 rows selected.

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=327 Bytes=8502)

1 0SORT (GROUP BY) (Cost=4 Card=327 Bytes=8502)

2 1TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327 Bytes=8502)

Statistics

----------------------------------------------------------

17recursive calls

0db block gets

25consistent gets

4physical reads

我们可以看到查询使用了查询重写的功能，直接查询物化视图中的查询方案，而不是查询其表，逻辑IO只有25个，性能相当良好。

假如这时候我们要按季度来查询统计销量信息，结果又会是怎样呢？

Roby@XUE> select time_hierarchy.qtr_yyyy, sum(sales_amount)

2from sales, time_hierarchy

3where sales.trans_date = time_hierarchy.day

4group by time_hierarchy.qtr_yyyy

5/

QTR_YYYYSUM(SALES_AMOUNT)

------------------------------------------------ -----------------

Q1 FY20061.1696E+12

Q1 FY20071.2297E+10

Q2 FY20061.1281E+12

Q3 FY20061.1389E+12

Q4 FY20061.1356E+12

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=5 Bytes=145)

10 SORT (GROUP BY) (Cost=1681 Card=5 Bytes=145)

21 NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)

32 TABLE ACCESS (FULL) OF 'SALES' (Cost=35 Card=426672

42 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)

Statistics

----------------------------------------------------------

14recursive calls

0db block gets

428048consistent gets

599physical reads

可以看到查询将直接查询基表产生了将近428048个逻辑IO，性能无法满足需求。

接下我们创建一个Dimension表time_hierarchy_dim，用于提醒优化器time_hierarchy表中的DAY列暗示着MMYYYY，MMYYYY又意味着QTY_YYYY，QTY_YYYY又意味着YYYY。然后我们将重新运行上面那个查询，看执行计划发生了怎样的变更。

Roby@XUE> create dimension time_hierarchy_dim

2level day is time_hierarchy.day

3level mmyyyy is time_hierarchy.mmyyyy

4level qtr_yyyy is time_hierarchy.qtr_yyyy

5level yyyy is time_hierarchy.yyyy

6hierarchy time_rollup

7(

8day child of

9mmyyyy child of

10qtr_yyyy child of

11yyyy

12)

13attribute mmyyyy

14determines mon_yyyy;

Dimension created.

Roby@XUE> select time_hierarchy.qtr_yyyy, sum(sales_amount)

2from sales, time_hierarchy

3where sales.trans_date = time_hierarchy.day

4group by time_hierarchy.qtr_yyyy

5/

QTR_YYYYSUM(SALES_AMOUNT)

------------------------------------------------ -----------------

Q1 FY20061.1696E+12

Q1 FY20071.2297E+10

Q2 FY20061.1281E+12

Q3 FY20061.1389E+12

Q4 FY20061.1356E+12

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=5 Bytes=195)

1 0SORT (GROUP BY) (Cost=14 Card=5 Bytes=195)

2 1HASH JOIN (Cost=7 Card=1157 Bytes=45123)

3 2VIEW (Cost=4 Card=46 Bytes=598)

4 3SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)

5 4INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)

6 2TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327

Statistics

----------------------------------------------------------

193recursive calls

0db block gets

49consistent gets

2physical reads

可以看到创建Dimension后，Oracle已经能够智能地理解交易日期中月度和季度的转换关系，查询使用到物化视图，逻辑IO由原来的428048个减少到49个，性能有了大幅的提升。

同样我们再来统计一下年度的销量信息：

Roby@XUE> select time_hierarchy.yyyy, sum(sales_amount)

2from sales, time_hierarchy

3where sales.trans_date = time_hierarchy.day

4group by time_hierarchy.yyyy

5/

YYYY SUM(SALES_AMOUNT)

---------- -----------------

20064.5721E+12

20071.2297E+10

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=2 Bytes=66)

10 SORT (GROUP BY) (Cost=10 Card=2 Bytes=66)

21 HASH JOIN (Cost=7 Card=478 Bytes=15774)

--End--