<>

对维度、实体化视图、查询重写的理解

http://docs.google.com/Doc?id=dgdk3pdk_48fmw6pd

以前，我对实体化视图的理解仍停留在8I的快照概念上，虽然知道它可以做统计，但对维的理解仍不清楚，查询重写也是知其一不知其二，很惭愧总是在MV上做查询。
这次正好项目中的统计用到了MV，以及日报表、月报表，于是想使用MV来解决一些问题。以下是参考AKADIA的资料所做的两个测试。只看白皮书会知道个大概，还需要实际操练一下。

测试一说明了实体化视图的查询重写功能，可以改善查询性能；
测试二说明了如何让实体化视图与维度结合起来使用。
更深入的比如在MV上聚合、子集等内容没有研究。话说过来，既然查询重写的结果是读MV，那么不如我直接读MV来操作。但是，举一返三，会用时间维，就可以延伸使用其它维，比如公安单位，也可以建立一个单位维表，在保存日报表时，保存派出所的单位代码，在维表中保存其上级、上上级代码名称。

-- 测试一

-- create user and grant it
SQL> create user dw identified by dw;
SQL> alter user dw default tablespace users;
SQL> grant connect,resource to dw;
grant create dimension to dw;
SQL> show parameter compati
SQL> show parameter query_rewrite
SQL> grant query rewrite to dw;
SQL> grant create materialized view to dw;

-- conn dw and test it.
sqlplus dw/dw
set echo on
set termout off

drop table bigtab;

create table bigtab
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/

insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;

analyze table bigtab compute statistics;
select count(*) from bigtab;

COUNT(*)
----------
708456

Initially this quewry will require a full scan of the large table.

set autotrace on
set timing on
select owner, count(*) from bigtab group by owner;

OWNER COUNT(*)
------------------------------ ----------
CTXSYS 6264
ELAN 1272
HR 816
......

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=878 Card=9 Bytes=45)
1 0 SORT (GROUP BY) (Cost=878 Card=9 Bytes=45)
2 1 TABLE ACCESS (FULL) OF 'BIGTAB' (Cost=396 Card=327648 By
tes=1638240)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4032 consistent gets
1503 physical reads
0 redo size
592 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

set autotrace off

alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

create materialized view mv_bigtab
build immediate
refresh on commit
enable query rewrite
as
select owner, count(*)
from bigtab
group by owner
/

set autotrace traceonly
select owner,count(*) from bigtab group by owner;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9 Bytes=90)
1 0 TABLE ACCESS (FULL) OF 'MV_BIGTAB' (Cost=2 Card=9 Bytes=90
)

Statistics
----------------------------------------------------------
25 recursive calls
0 db block gets
19 consistent gets
1 physical reads
0 redo size
592 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> set timing off
SQL> set autotrace off
SQL> drop materialized view mv_bigtab;
SQL> drop table bigtab;
-- 结论：使用MV的查询重写功能，可以减少一致性读和物理读。
-- 测试二

Fact table: Sales
Dimension Table: Time

create user dw identified by dw;
alter user dw default tablespace users;
grant connect,resource to dw;
grant create dimension to dw;
show parameter compati
show parameter query_rewrite
grant query rewrite to dw;
grant create materialized view to dw;

-- Setup of the sales table
CREATE TABLE sales (
trans_date DATE,
cust_id INT,
sales_amount NUMBER
);

INSERT /*+ APPEND */ INTO sales
SELECT TRUNC(SYSDATE,'YYYY')+MOD(ROWNUM,366) trans_date,
MOD(ROWNUM,100) cust_id,
ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
FROM all_objects
/
COMMIT;

-- 再多插入一些数据
BEGIN
FOR i IN 1 .. 10
LOOP
INSERT /*+ APPEND */ INTO sales
SELECT trans_date, cust_id,
ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
FROM sales;
COMMIT;
END LOOP;
END;
/

SELECT COUNT(*) FROM sales;

COUNT(*)
----------
3494912

-- Setup of the TIME table
drop table time;
CREATE TABLE time (
day PRIMARY KEY,
mmyyyy,
mon_yyyy,
qtr_yyyy,
yyyy
)
ORGANIZATION INDEX
AS
SELECT DISTINCT
trans_date DAY,
CAST (TO_CHAR(trans_date,'MMYYYY') AS NUMBER) MMYYYY,
TO_CHAR(trans_date,'MON-YYYY') MON_YYYY,
'Q' || CEIL(TO_CHAR(trans_date,'MM')/3) || ' FY'
|| TO_CHAR(trans_date,'YYYY') QTR_YYYY,
CAST(TO_CHAR(trans_date, 'YYYY') AS NUMBER) YYYY
FROM sales
/

-- Setup of the SALES_MV materialized view
ANALYZE TABLE SALES COMPUTE STATISTICS;
ANALYZE TABLE TIME COMPUTE STATISTICS;

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT sales.cust_id,
SUM(sales.sales_amount) sales_amount,
time.mmyyyy
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY sales.cust_id, time.mmyyyy
/

ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

SET AUTOTRACE ON

SELECT time.mmyyyy, SUM(sales_amount)
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.mmyyyy
/
SET AUTOTRACE OFF

......
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=572 Bytes=148
72)

1 0 SORT (GROUP BY) (Cost=5 Card=572 Bytes=14872)
2 1 TABLE ACCESS (FULL) OF 'SALES_MV' (Cost=2 Card=572 Bytes=14872)
......
Oracle rewrote the query to use the materialized view SALES_MV (see Execution Plan).
However, let's see what happens if we issue a query that calls for a higher level of aggregation.

SET TIMING ON
SET AUTOTRACE ON
SELECT time.qtr_yyyy, SUM(sales_amount)
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.qtr_yyyy
/
SET AUTOTRACE OFF

QTR_YYYY SUM(SALES_AMOUNT)
---------- -----------------
Q1 FY2002 1.2507E+12
Q1 FY2003 1.3510E+10
Q2 FY2002 1.2674E+12
Q3 FY2002 1.2762E+12
Q4 FY2002 1.2672E+12

Elapsed: 00:00:16.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14571 Card=5 Bytes=1
45)

1 0 SORT (GROUP BY) (Cost=14571 Card=5 Bytes=145)
2 1 NESTED LOOPS (Cost=1084 Card=3496960 Bytes=101411840)
3 2 TABLE ACCESS (FULL) OF 'SALES' (Cost=1084 Card=3496960 Bytes=45460480)
4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_9678' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3508089 consistent gets
9091 physical reads
660 redo size
586 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed

We see that Oracle does not yet that it could have used the materialized view to answer this particular query, so it used the original SALES table instead, and had to do a lot of work to get the answer. The same bad thing would happen if we requested data aggregated by fiscal year.

-- Setup of the TIME_DIM dimension
CREATE DIMENSION time_dim
LEVEL DAY IS time.day
LEVEL MMYYYY IS time.mmyyyy
LEVEL QTR_YYYY IS time.qtr_yyyy
LEVEL YYYY IS time.yyyy
HIERARCHY TIME_ROLLUP
(
day CHILD OF
mmyyyy CHILD OF
qtr_yyyy CHILD OF
yyyy
)
ATTRIBUTE mmyyyy DETERMINES mon_yyyy; -- attribute 是说明mmyyyy 决定 mon_yyyy的值

/*
CREATE TABLE time (
day PRIMARY KEY,
mmyyyy,
mon_yyyy,
qtr_yyyy,
yyyy
)*/

conn dw/dw
SET TIMING ON
SET AUTOTRACE ON
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

SELECT time.qtr_yyyy, SUM(sales_amount)
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.qtr_yyyy
/
SET AUTOTRACE OFF

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=5 Bytes=195)
1 0 SORT (GROUP BY) (Cost=18 Card=5 Bytes=195)
2 1 HASH JOIN (Cost=7 Card=2024 Bytes=78936)
3 2 VIEW (Cost=4 Card=46 Bytes=598)
4 3 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
5 4 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_9678' (UNIQUE) (Cost=2 Card=366 Bytes=4758)

6 2 TABLE ACCESS (FULL) OF 'SALES_MV' (Cost=2 Card=572 Bytes=14872)

-- 同样也可以使用上MV