Study to use Materialized View--笔记一

最新推荐文章于 2023-03-13 14:30:48 发布
最新推荐文章于 2023-03-13 14:30:48 发布
阅读量586 收藏
点赞数
分类专栏： Oracle数据库学习 文章标签： hierarchy table query oracle date session
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yuast1234/article/details/5420224
版权

 

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


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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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


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

| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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.

 

yuast1234
关注
专栏目录
学习ORACLE-物化视图（MATERIALIZED VIEW）总结
找个地方记笔记
01-09 1万+
数据仓库项目中使用物化视图推送数据，现记录一下相关方面的知识
oracle row 00033,Oracle 导出错误 EXP-00000~EXP-00107
weixin_28717647的博客
04-03 566
EXP-00000: Export terminated unsuccessfullyCause:Export encountered an Oracle error.Action:Look up the accompanying Oracle message in the ORA message chapters of this manual, and take appropriate ac...
Oracle使用物化视图提高group by性能
落叶无声的专栏
01-04 4162
情况介绍 现有表OE.PRODUCT_INFORMATION数据量为800万，求出各个供应商供应产品的数量 无使用物化视图，只在建立SUPPLIER_ID建立索引，执行时间为15s左右SELECT SUPPLIER_ID, COUNT(*) FROM OE.PRODUCT_INFORMATION GROUP BY SUPPLIER_ID ORDER BY SUPPLIER_ID; 使用物化视图(M
Oracle 物化视图之--复杂物化视图创建
天涯客*Blog
12-07 5221
Oracle 物化视图之--复杂物化视图创建 包含聚集的物化视图 1.必须满足所有快速刷新物化视图都满足的条件； 2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制： (1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列； (2)必须指明ROWID和INCLUDING NEW VALUES； (3)如果对基本的操作同时包括
ORACLE物化视图-通过Oracle Dimension来优化物化视图的查询
oracel数据库、数据仓库、JAVA开发的专栏
02-02 1815
http://hi.baidu.com/edeed/blog/item/014a7becfa6a15d32f2e21b0.html在数据仓库环境中，我们通常利用物化视图强大的查询重写功能来提升统计查询的性能，但是物化视图的查询重写功能有时候无法智能地判断查询中一些相关联的条件，以至于影响性能。比如我们有一张销售表sales，用于存储订单的详细信息，包含交易日期、顾客编号和销售量。我们创建一
Study to use Materialized View--笔记二
RobinHood
04-02 382
MV的刷新方式及MV log----还没写完！
学习笔记：Rick's RoTs -- Rules of Thumb for MySQL
aecuhty88306453的博客
12-09 288
Table of Contents SELECTs -- do's and don'tsINDEXingENGINE DifferencesOptimizations, and notPARTITIONingMemory UsageCharacter SetsDatatypes - Directly supportedDatatypes - ImplicitHardwa...
Oracle Database 数据库 笔记
最新发布
云满笔记
03-13 891
Oracle Database 数据库 笔记
高负载高并发网站架构分析
热门推荐
leirainy的专栏
12-21 5万+
由于自己正在做一个高性能大用户量的论坛程序，对高性能高并发服务器架构比较感兴趣，于是在网上收集了不少这方面的资料和大家分享。希望能和大家交流 msn: defender_ios@hotmail.com ———————————————————————————————————————  初创网站与开源软件 6  谈谈大型高负载网站服务器的优化心得! 8  Lighttpd+S
PostgresQL-Materialized-View-Demo:PostgresQL中物化视图的演示脚本
04-17
Postgres物化视图演示 设置 在您的机器上安装了postgresql 运行脚本 createdb your_database psql -f setup_scripts/00_setup.sql psql -d your_database 如果需要更多种子数据， bundle exec ruby dev.rb以为...
PostgreSQL物化视图（materialized view）过程解析
12-14
这篇文章主要介绍了PostgreSQL物化视图（materialized view）过程解析,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 1、创建视图 CREATE MATERIALIZED VIEW ...
Magento-Enterprise-Materialized-Views-Example
06-28
这个存储库是一个示例 Magento 扩展，它使用企业版 1.13 中的新 Mview 组件。 它是作为博客文章的一部分构建的，位于 安装 要安装此扩展，只需将其复制到您的基本 Magento 安装目录中，因为它以 Magento 核心文件的 ...
cosmosdb-materialized-views：一个完整​​的示例，该示例显示如何使用CosmosDB，Change Feed和Azure函数实现实时更新的Materalized视图。
02-03
此类数据由Cosmos DB Change Feed公开，并由Azure Function（通过Change Feed Processor）使用，该功能获取JSON文档并使用它创建或更新存储在view集中的相关物化视图。 可以在这里找到有关体系结构和解决方案的更...
Oracle MATERIALIZED VIEW -- 物化视图
继续前行
06-17 537
1，ORACLE：materialized view和普通VIEW有什么区别 materialized view(MV)是自动刷新或者手动刷新的，View不用刷新 MV也可以直接update，但是不影响base table，对View的update反映到base table上 MV主要用于远程数据访问，mv中的数据需要占用磁盘空间，view中不保存数据。 2，权限 必须具有 RES...
SQLPLUS用法【一】：define和variable用法的比较
RobinHood
08-21 1809
define usage:define定义的是用户变量。define X=32pass a char type whose value is 32 to the variable X.Use &X to implement in pl/sql block;define xdisplay the value of user variable x. eg. DEFINE X
oracle中；和 / 的区别
RobinHood
08-11 1637
在sqlplus中， oracle 用/ 或者 ; 作为sql语句的终结符。 注意一个符号就可以终结！ 而在plsql中， 终结符就只有/，因此如begin  dbms_output.put_line(Hello!);end; 这段代码不会运行。 而正确的写法是begin  dbms_output.put_line(Hello!);end;/ 之
不用select * 的原因收集
RobinHood
05-07 1550
1.减少parse开销2.防止mess data when using invoker right.
oracle concurrency -- serializable and read only
RobinHood
08-31 1124
 oracle中最严格的控制并发的两个特性： Set transaction read only: session1session2set transaction read only name rxyu; select * from emp;update emp set mgr_id=100 where emp_id=100;co
常用到的query
RobinHood
11-04 900
<br />how to have one session id in oracle:<br />SQL> select sys_context('USERENV','sid') from dual; <br />HOw to have the lock of this session<br />select s.osuser,l.id1,l.id2,l.lmode,l.request,l.block,l.type <br />from v$session s, v$lock l <
Oracle Materialized View详解与创建方法
例如，主键物化视图（CREATE MATERIALIZED VIEW mv_emp_pk）通过`CREATE MATERIALIZED VIEW`语句创建，如示例所示，`REFRESH FAST`选项确保快速刷新视图，而`START WITH SYSDATE`指定初始刷新时间，`NEXT SYSTEM ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值