现在的位置: 首页 > 综合 > 正文

Study to use Materialized View–笔记一

2018年04月17日 ⁄ 综合 ⁄ 共 11135字 ⁄ 字号小中大 ⁄ 评论关闭

Study to Use Materialized View

Here today i am here to study Materialize View. As the information explosion, many GB,TB data is stored in Database, techniquely in tables.

For these big talbe, it is very slow if you just query it somtime just for some aggregation information. So Oracle suggest to use Materialized View.

Here is i going to introduce it from following aspects:

a.privilege needed

b.parameter or call Environment Setting

c.Syntax

d.dimension & OLAP

Privileges needed:

create table;

create materialized view

create dimension

query rewrite

Parameter or Called Environment Setting

alter session set query_rewrite_enabled = true;

alter session set query_rewrite_integrity=[enforced|trusted|stale_tolerated]

For query_rewrite_integrity parameter,

enforced: rewritten query shouldn't get the wrong data or break any validation rules. Normally the query need to be as same as the mv definition.

trusted: have rule information to let Oracle but Oracle not enforce it. Oracle trust our rules.(typically used in Data warehouse )

Stale_tolerated: used in Reporting program.

Syntax:

Here i will use the example to show syntax and also some feature described in above chapter.

-->example I

15:57:11 lab@ORCL>alter session set query_rewrite_enabled =true;

会话已更改。

已用时间: 00: 00: 00.00

15:57:32 lab@ORCL>alter session set query_rewrite_integrity =enforced;

会话已更改。

已用时间: 00: 00: 00.01

15:57:34 lab@ORCL>create table emp as select * from scott.emp;

表已创建。

已用时间: 00: 00: 00.14

15:57:45 lab@ORCL>create table dept as select * from scott.dept;

表已创建。

已用时间: 00: 00: 00.06

15:57:46 lab@ORCL>create materialized view emp_dept

15:57:53 2 build immediate

15:57:53 3 refresh on demand

15:57:53 4 enable query rewrite

15:57:53 5 as

15:57:53 6 select d.deptno,count(*), d.dname

15:57:53 7 from emp e, dept d

15:57:53 8 where e.deptno = d.deptno

15:57:53 9 group by d.deptno,d.dname;

实体化视图已创建。

已用时间: 00: 00: 00.37

15:57:54 lab@ORCL>set autotrace traceonly

15:58:06 lab@ORCL>select count(*) from emp;

已选择 1 行。

已用时间: 00: 00: 00.00

执行计划

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

Plan hash value: 2083865914

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

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

| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| EMP | 56 | 3 (0)| 00:00:01 | -->without constraint, go for full table scan

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

15:58:20 lab@ORCL>alter table dept add constraint dept_pk primary key(deptno);

表已更改。

已用时间: 00: 00: 00.03

15:58:40 lab@ORCL>alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno);

表已更改。

已用时间: 00: 00: 00.03

15:58:40 lab@ORCL>alter table emp modify deptno not null;

表已更改。

已用时间: 00: 00: 00.01

15:58:42 lab@ORCL>select count(*) from emp;

已选择 1 行。

已用时间: 00: 00: 00.09

执行计划

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

Plan hash value: 155013515

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT | 8 | 104 | 3 (0)| 00:00:01 | -->with constraints, go for MView

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

15:59:13 lab@ORCL>alter table emp drop constraint emp_fk_dept;

表已更改。

已用时间: 00: 00: 00.01

15:59:39 lab@ORCL>alter table dept drop constraint dept_pk;

表已更改。

已用时间: 00: 00: 00.04

15:59:39 lab@ORCL>alter table emp modify deptno null;

表已更改。

已用时间: 00: 00: 00.01

16:03:45 lab@ORCL>update emp set deptno=null where empno=8876; -->update to make the data voliate the constraint

已更新 1 行。

已用时间: 00: 00: 00.01

16:04:11 lab@ORCL>commit;

提交完成。

已用时间: 00: 00: 00.01 -->cheat Oracle all the data comply to the rule.

16:04:13 lab@ORCL>alter table dept add constraint dept_pk primary key(deptno) rely enable novalidate;

表已更改。

已用时间: 00: 00: 00.04

16:05:37 lab@ORCL>alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) rely enable novalidate;

表已更改。

已用时间: 00: 00: 00.03

16:05:38 lab@ORCL>alter table emp modify deptno not null novalidate;

表已更改。

已用时间: 00: 00: 00.06

16:13:45 lab@ORCL>exec dbms_mview.refresh('EMP_DEPT');

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.06

16:17:09 lab@ORCL>alter session set query_rewrite_integrity=enforced;

会话已更改。

已用时间: 00: 00: 00.01

16:17:24 lab@ORCL>select count(*) from emp;

已选择 1 行。

已用时间: 00: 00: 00.00

执行计划

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

Plan hash value: 2083865914

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

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

| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| EMP | 56 | 3 (0)| 00:00:01 | -->can't go for MV because of one corrupted data.

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

16:17:45 lab@ORCL>alter session set query_rewrite_integrity=trusted;

会话已更改。

已用时间: 00: 00: 00.00

16:17:53 lab@ORCL>select count(*) from emp;

已选择 1 行。

已用时间: 00: 00: 00.01

执行计划

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

Plan hash value: 155013515

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT | 8 | 104 | 3 (0)| 00:00:01 |-->although data corrupted, cheat Oracle not.

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

Dimension:

16:26:46 lab@ORCL>create table sales(trans_date date, cust_id int,sals_amount number);

表已创建。

已用时间: 00: 00: 00.09

16:27:57 lab@ORCL>insert /*+ APPEND */ into sales select trunc(sysdate,'yyyy') + mod(rownum,366) trans_date,

16:27:57 2 mod(rownum,100) cust_id, abs(dbms_random.random)/100 Sales_amount from all_objects

16:27:57 3 /

已创建40729行。

已用时间: 00: 00: 05.12

16:28:02 lab@ORCL>commit;

提交完成。

已用时间: 00: 00: 00.01

16:28:02 lab@ORCL>begin

16:28:02 2 for i in 1..4 loop

16:28:02 3 --if use /*+ APPEND */, will have error. can't modify it when ready it in parallel mode.

16:28:02 4 insert into sales select trans_date,cust_id,abs(dbms_random.random)/100 from sales;

16:28:02 5 end loop;

16:28:02 6 commit;

16:28:02 7 end;

16:28:02 8 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 08.01

16:28:10 lab@ORCL>create table time_hierarchy(day primary key, mmyyyy, mon_yyyy,qtr_yyyy,yyyy)

16:28:10 2 organization index

16:28:10 3 as

16:28:10 4 select distinct

16:28:10 5 trans_date day,

16:28:10 6 cast(to_char(trans_date,'mmyyyy') as number) mmyyyy,

16:28:10 7 to_char(trans_date,'mmyyyy') mon_yyyy,

16:28:10 8 'Q'||ceil(to_char(trans_date,'mm')/3)||'FY'||to_char(trans_date,'yyyy') QTR_yyyy,

16:28:10 9 cast(to_char(trans_date,'yyyy') as number) yyyy

16:28:10 10 from sales

16:28:10 11 /

表已创建。

已用时间: 00: 00: 04.04

16:28:14 lab@ORCL>create materialized view sales_mv

16:28:14 2 build immediate

16:28:14 3 refresh on demand

16:28:14 4 enable query rewrite --very important, without the following query will not rewritten by the Oracle to use mv.

16:28:14 5 as

16:28:14 6 select s.cust_id,sum(s.sals_amount) sum_amount,t.mmyyyy

16:28:14 7 from sales s, time_hierarchy t

16:28:14 8 where s.trans_date=t.day

16:28:14 9 group by s.cust_id,t.mmyyyy

16:28:14 10 /

实体化视图已创建。

已用时间: 00: 00: 01.06

16:28:15 lab@ORCL>analyze table sales_mv compute statistics

16:28:15 2 /

表已分析。

16:28:48 lab@ORCL>set autotrace traceonly

16:30:43 lab@ORCL>rem --montly

16:30:43 lab@ORCL>select t.mmyyyy,sum(s.sals_amount) sum_amount

16:30:43 2 from sales s, time_hierarchy t

16:30:43 3 where s.trans_date=t.day

16:30:43 4 group by t.mmyyyy;

已选择13行。

已用时间: 00: 00: 00.04

执行计划

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

Plan hash value: 4087183010

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

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

| 0 | SELECT STATEMENT | | 13 | 143 | 4 (25)| 00:00:01 |

| 1 | HASH GROUP BY | | 13 | 143 | 4 (25)| 00:00:01 |

| 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1250 | 13750 | 3 (0)| 00:00:01 |-->used under "trusted" mode. siliar to MV definition

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

16:30:43 lab@ORCL>rem --before create dimension

16:30:43 lab@ORCL>rem --quarterly

16:30:43 lab@ORCL>select t.qtr_yyyy,sum(s.sals_amount) sum_amount

16:30:43 2 from sales s, time_hierarchy t

16:30:43 3 where s.trans_date=t.day

16:30:43 4 group by t.qtr_yyyy;

已选择5行。

已用时间: 00: 00: 00.70

执行计划

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

Plan hash value: 3996723075

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

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

| 0 | SELECT STATEMENT | | 605K| 32M| 584 (22)| 00:00:08 |

| 1 | HASH GROUP BY | | 605K| 32M| 584 (22)| 00:00:08 |

|* 2 | HASH JOIN | | 605K| 32M| 493 (7)| 00:00:06 |

| 3 | INDEX FAST FULL SCAN| SYS_IOT_TOP_54478 | 366 | 12444 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | SALES | 605K| 12M| 479 (5)| 00:00:06 | -->for quarter, semantically it can but oracle can't

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

So Oracle suggested DIMENSION

16:35:19 lab@ORCL>rem -creating dimension............

16:35:23 lab@ORCL>create dimension time_hierarchy_dim

16:35:23 2 level day is time_hierarchy.day

16:35:23 3 level mmyyyy is time_hierarchy.mmyyyy

16:35:23 4 level qtr_yyyy is time_hierarchy.qtr_yyyy

16:35:23 5 level yyyy is time_hierarchy.yyyy

16:35:23 6 hierarchy time_rollup

16:35:23 7 (

16:35:23 8 day child of mmyyyy child of qtr_yyyy child of yyyy)

16:35:24 9 attribute mmyyyy

16:35:24 10 determines mon_yyyy

16:35:24 11 /

维已创建。

已用时间: 00: 00: 00.01

16:35:25 lab@ORCL>rem --after create dimension

16:36:20 lab@ORCL>select t.qtr_yyyy,sum(s.sals_amount) sum_amount

16:36:20 2 from sales s, time_hierarchy t

16:36:20 3 where s.trans_date=t.day

16:36:20 4 group by t.qtr_yyyy;

已选择5行。

已用时间: 00: 00: 00.06

执行计划

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

Plan hash value: 4278642376

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

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

| 0 | SELECT STATEMENT | | 35192 | 1683K| 13 (54)| 00:00:01 |

| 1 | HASH GROUP BY | | 35192 | 1683K| 13 (54)| 00:00:01 |

|* 2 | HASH JOIN | | 35192 | 1683K| 8 (25)| 00:00:01 |

| 3 | VIEW | | 366 | 13908 | 4 (25)| 00:00:01 |

| 4 | HASH UNIQUE | | 366 | 13908 | 4 (25)| 00:00:01 |

| 5 | INDEX FAST FULL SCAN | SYS_IOT_TOP_54478 | 366 | 13908 | 3 (0)| 00:00:01 |

| 6 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1250 | 13750 | 3 (0)| 00:00:01 | -->with DIMENSION, it can use MV again.

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

From all these practices , we can see.

1. Oracle could rewrite our query with the help of MV.

2. Oracle rewrite our query need data-dict information as well as session parameter.

3. Semantic relationship can be implemented using dimension so as to let Oracle use MV.

All in All, Oracle use MV to help us to improve profermance especially for Data warehousing Project.

【上篇】Stream Advanced Queue–学习笔记一
【下篇】10g New Feature–Collect function

作者: soyo

该日志由 soyo 于6年前发表在综合分类下，最后更新于 2018年04月17日.
转载请注明: Study to use Materialized View–笔记一 | 学步园 +复制链接

抱歉!评论已关闭.

学步园