Oracle Dimension 下 (ZT)

2006-02-28 16:33

我们再创建一张customer_hierarchy表，用于存储客户代码、邮政编码和地区的关系，然后我们将按不同邮编或地区来查询各自的月度、季度或者年度销量信息。

Roby@XUE> create table customer_hierarchy

2( cust_id primary key, zip_code, region )

3organization index

4as

5select cust_id,

6mod( rownum, 6 ) || to_char(mod( rownum, 1000 ), 'fm0000') zip_code,

7mod( rownum, 6 ) region

8from ( select distinct cust_id from sales)

9/

Table created.

Roby@XUE> analyze table customer_hierarchy compute statistics;

Table analyzed.

改写物化视图，查询方案中添加按不同邮编的月度统计销量。

Roby@XUE> drop materialized view mv_sales;

Materialized view dropped.

Roby@XUE> create materialized view mv_sales

2build immediate

3refresh on demand

4enable query rewrite

5as

6select customer_hierarchy.zip_code,

7time_hierarchy.mmyyyy,

8sum(sales.sales_amount) sales_amount

9from sales, time_hierarchy, customer_hierarchy

10where sales.trans_date = time_hierarchy.day

11and sales.cust_id = customer_hierarchy.cust_id

12group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy

13/

Materialized view created.

Roby@XUE> set autotrace traceonly

Roby@XUE> select customer_hierarchy.zip_code,

2time_hierarchy.mmyyyy,

3sum(sales.sales_amount) sales_amount

4from sales, time_hierarchy, customer_hierarchy

5where sales.trans_date = time_hierarchy.day

6and sales.cust_id = customer_hierarchy.cust_id

7group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy

8/

1216 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=409 Bytes=20450)

1 0TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 Bytes=20450)

Statistics

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

28recursive calls

0db block gets

116consistent gets

5physical reads

可以看到如果按不同邮编、不同月度来统计查询的话，优化器将会查询物化视图中的查询方案，性能也是比较可观的。假如我们查不同地区年度的统计销量信息，结果又会是怎样？

Roby@XUE> select customer_hierarchy.region,

2time_hierarchy.yyyy,

3sum(sales.sales_amount) sales_amount

4from sales, time_hierarchy, customer_hierarchy

5where sales.trans_date = time_hierarchy.day

6and sales.cust_id = customer_hierarchy.cust_id

7group by customer_hierarchy.region, time_hierarchy.yyyy

8/

9 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=9 Bytes=261)

1 0SORT (GROUP BY) (Cost=1681 Card=9 Bytes=261)

2 1NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)

3 2NESTED LOOPS (Cost=35 Card=426672 Bytes=8106768)

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

5 3INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)

6 2INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)

Statistics

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

0recursive calls

0db block gets

428047consistent gets

745physical reads

可以看到查询性能大有影响。接下我们同样创建dimension sales_dimension，用于说明客户代码和邮编、地区间的关系：

Roby@XUE> drop dimension time_hierarchy_dim

2/

Dimension dropped.

Roby@XUE> create dimension sales_dimension

2level cust_id is customer_hierarchy.cust_id

3level zip_code is customer_hierarchy.zip_code

4level region is customer_hierarchy.region

5level day is time_hierarchy.day

6level mmyyyy is time_hierarchy.mmyyyy

7level qtr_yyyy is time_hierarchy.qtr_yyyy

8level yyyy is time_hierarchy.yyyy

9hierarchy cust_rollup

10(

11cust_id child of

12zip_code child of

13region

14)

15hierarchy time_rollup

16(

17day child of

18mmyyyy child of

19qtr_yyyy child of

20yyyy

21)

22attribute mmyyyy

23determines mon_yyyy;

Dimension created.

再回到原来的查询，我们可以看到查询性能有了大幅的提升：

Roby@XUE> set autotrace on

Roby@XUE> select customer_hierarchy.region,

2time_hierarchy.yyyy,

3sum(sales.sales_amount) sales_amount

4from sales, time_hierarchy, customer_hierarchy

5where sales.trans_date = time_hierarchy.day

6and sales.cust_id = customer_hierarchy.cust_id

7group by customer_hierarchy.region, time_hierarchy.yyyy

8/

REGIONYYYY SALES_AMOUNT

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

02006 7.3144E+11

02007 4484956329

12006 7.8448E+11

22006 7.7257E+11

22007 4684418980

32006 7.7088E+11

42006 7.8004E+11

42007 3127953246

52006 7.3273E+11

9 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=9 Bytes=576)

1 0SORT (GROUP BY) (Cost=15 Card=9 Bytes=576)

2 1HASH JOIN (Cost=10 Card=598 Bytes=38272)

3 2VIEW (Cost=3 Card=100 Bytes=700)

4 3SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)

5 4INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)

6 2HASH JOIN (Cost=7 Card=598 Bytes=34086)

7 6VIEW (Cost=4 Card=19 Bytes=133)

8 7SORT (UNIQUE) (Cost=4 Card=19 Bytes=133)

9 8INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828'

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

Statistics

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

364recursive calls

0db block gets

88consistent gets

0physical reads

Roby@XUE> set autot trace

Roby@XUE> select customer_hierarchy.region,

2time_hierarchy.qtr_yyyy,

3sum(sales.sales_amount) sales_amount

4from sales, time_hierarchy, customer_hierarchy

5where sales.trans_date = time_hierarchy.day

6and sales.cust_id = customer_hierarchy.cust_id

7group by customer_hierarchy.region, time_hierarchy.qtr_yyyy;

27 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=22 Bytes=154

10 SORT (GROUP BY) (Cost=23 Card=22 Bytes=1540)

21 HASH JOIN (Cost=11 Card=1447 Bytes=101290)

32 VIEW (Cost=3 Card=100 Bytes=700)

43 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)

54 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE) (

62 HASH JOIN (Cost=7 Card=1447 Bytes=91161)

76 VIEW (Cost=4 Card=46 Bytes=598)

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

98 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UN

106 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B

Statistics

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

10recursive calls

0db block gets

19consistent gets

0physical reads

Roby@XUE> select customer_hierarchy.region,

2time_hierarchy.mon_yyyy,

3sum(sales.sales_amount) sales_amount

4from sales, time_hierarchy, customer_hierarchy

5where sales.trans_date = time_hierarchy.day

6and sales.cust_id = customer_hierarchy.cust_id

7group by customer_hierarchy.region, time_hierarchy.mon_yyyy;

75 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=56 Bytes=386

10 SORT (GROUP BY) (Cost=41 Card=56 Bytes=3864)

21 HASH JOIN (Cost=11 Card=3775 Bytes=260475)

32 VIEW (Cost=4 Card=120 Bytes=1440)

43 SORT (UNIQUE) (Cost=4 Card=120 Bytes=1440)

54 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQ

62 HASH JOIN (Cost=6 Card=409 Bytes=23313)

76 VIEW (Cost=3 Card=100 Bytes=700)

87 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)

98 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)

106 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B

Statistics

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

0recursive calls

0db block gets

14consistent gets

0physical reads

参考：Tomates Kyte 《Expert One-on-One Oracle》

--End--