Oracle Dimension 下alibaba - 千亿集团

Oracle Dimension 下alibaba

2019-02-08 10:32:04 | 作者: 恨山 | 标签: 邮编,区域,月度 | 浏览: 3346

咱们再创立一张customer_hierarchy表,用于存储客户代码、邮政编码和区域的联系,然后咱们将按不同邮编或区域来查询各自的月度、季度或许年度销量信息。

Roby@XUE create table customer_hierarchy

2 ( cust_id primary key, zip_code, region )

3 organization index

4 as

5 select cust_id,

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

7 mod( rownum, 6 ) region

8 from ( 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

2 build immediate

3 refresh on demand

4 enable query rewrite

5 as

6 select customer_hierarchy.zip_code,

7 time_hierarchy.mmyyyy,

8 sum(sales.sales_amount) sales_amount

9 from sales, time_hierarchy, customer_hierarchy

10 where sales.trans_date = time_hierarchy.day

11 and sales.cust_id = customer_hierarchy.cust_id

12 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy

13 /

Materialized view created.

Roby@XUE set autotrace traceonly

Roby@XUE select customer_hierarchy.zip_code,

2 time_hierarchy.mmyyyy,

3 sum(sales.sales_amount) sales_amount

4 from sales, time_hierarchy, customer_hierarchy

5 where sales.trans_date = time_hierarchy.day

6 and sales.cust_id = customer_hierarchy.cust_id

7 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy

8 /

1216 rows selected.

Execution Plan

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

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

Statistics

28 recursive calls

0 db block gets

116 consistent gets

5 physical reads

能够看到假设按不同邮编、不同月度来计算查询的话,优化器将会查询物化视图中的查询计划,功能也是比较可观的。假设咱们查不同区域年度的计算销量信息,成果又会是怎样?

Roby@XUE select customer_hierarchy.region,

2 time_hierarchy.yyyy,

3 sum(sales.sales_amount) sales_amount

4 from sales, time_hierarchy, customer_hierarchy

5 where sales.trans_date = time_hierarchy.day

6 and sales.cust_id = customer_hierarchy.cust_id

7 group by customer_hierarchy.region, time_hierarchy.yyyy

8 /

9 rows selected.

Execution Plan

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

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

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

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

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

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

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

Statistics

0 recursive calls

0 db block gets

428047 consistent gets

745 physical reads

能够看到查询功能大有影响。接下咱们相同创立dimension sales_dimension,用于阐明客户代码和邮编、区域间的联系:

Roby@XUE drop dimension time_hierarchy_dim

2 /

Dimension dropped.

Roby@XUE create dimension sales_dimension

2 level cust_id is customer_hierarchy.cust_id

3 level zip_code is customer_hierarchy.zip_code

4 level region is customer_hierarchy.region

5 level day is time_hierarchy.day

6 level mmyyyy is time_hierarchy.mmyyyy

7 level qtr_yyyy is time_hierarchy.qtr_yyyy

8 level yyyy is time_hierarchy.yyyy

9 hierarchy cust_rollup

10 (

11 cust_id child of

12 zip_code child of

13 region

14 )

15 hierarchy time_rollup

16 (

17 day child of

18 mmyyyy child of

19 qtr_yyyy child of

20 yyyy

21 )

22 attribute mmyyyy

23 determines mon_yyyy;

Dimension created.

再回到本来的查询,咱们能够看到查询功能有了大幅的提高:

Roby@XUE set autotrace on

Roby@XUE select customer_hierarchy.region,

2 time_hierarchy.yyyy,

3 sum(sales.sales_amount) sales_amount

4 from sales, time_hierarchy, customer_hierarchy

5 where sales.trans_date = time_hierarchy.day

6 and sales.cust_id = customer_hierarchy.cust_id

7 group by customer_hierarchy.region, time_hierarchy.yyyy

8 /

REGION YYYY SALES_AMOUNT

0 2006 7.3144E+11

0 2007 4484956329

1 2006 7.8448E+11

2 2006 7.7257E+11

2 2007 4684418980

3 2006 7.7088E+11

4 2006 7.8004E+11

4 2007 3127953246

5 2006 7.3273E+11

9 rows selected.

Execution Plan

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

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

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

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

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

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

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

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

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

9 8 INDEX (FAST FULL SCAN) OF SYS_IOT_TOP_7828

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

Statistics

364 recursive calls

0 db block gets

88 consistent gets

0 physical reads

Roby@XUE set autot trace

Roby@XUE select customer_hierarchy.region,

2 time_hierarchy.qtr_yyyy,

3 sum(sales.sales_amount) sales_amount

4 from sales, time_hierarchy, customer_hierarchy

5 where sales.trans_date = time_hierarchy.day

6 and sales.cust_id = customer_hierarchy.cust_id

7 group by customer_hierarchy.region, time_hierarchy.qtr_yyyy;

27 rows selected.

Execution Plan

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

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

2 1 HASH JOIN (Cost=11 Card=1447 Bytes=101290)

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

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

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

6 2 HASH JOIN (Cost=7 Card=1447 Bytes=91161)

7 6 VIEW (Cost=4 Card=46 Bytes=598)

8 7 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)

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

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

Statistics

10 recursive calls

0 db block gets

19 consistent gets

0 physical reads

Roby@XUE select customer_hierarchy.region,

2 time_hierarchy.mon_yyyy,

3 sum(sales.sales_amount) sales_amount

4 from sales, time_hierarchy, customer_hierarchy

5 where sales.trans_date = time_hierarchy.day

6 and sales.cust_id = customer_hierarchy.cust_id

7 group by customer_hierarchy.region, time_hierarchy.mon_yyyy;

75 rows selected.

Execution Plan

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

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

2 1 HASH JOIN (Cost=11 Card=3775 Bytes=260475)

3 2 VIEW (Cost=4 Card=120 Bytes=1440)

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

5 4 INDEX (FAST FULL SCAN) OF SYS_IOT_TOP_7828 (UNIQ

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

7 6 VIEW (Cost=3 Card=100 Bytes=700)

8 7 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)

9 8 INDEX (FULL SCAN) OF SYS_IOT_TOP_7833 (UNIQUE)

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

Statistics

0 recursive calls

0 db block gets

14 consistent gets

0 physical reads

 

参阅:Tomates Kyte 《Expert One-on-One Oracle》

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    按要求写sql句子itjob

    句子,学员,课程
  • 2

    检查数据库的SQL快报

    检查,数据库,检查表
  • 3

    DB2 备份和康复huabian

    康复,备份,数据库
  • 4

    运用MySQL头条

    运用,问题,效劳
  • 5
  • 6

    DATA PUMPfenghuang

    简略,指令,数据
  • 7

    Oracle Dimension 下alibaba

    邮编,区域,月度
  • 8

    Oracle失望锁和达观锁ITeyecsdn

    数据,时分,数据库
  • 9

    start with 用法ITeyeitjob

    子句,末梢,树形
  • 10

    mysql 根本指令ITeye头条

    用户,权限,体系