Oracle Dimension 下

aymjtxbzbp

　　我们再创建一张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》