|
|---|
|
|
|
|
|
|
g*******r
发帖数: 1414 | 1 数据库新手，请轻拍
想给现在的几个scheduling的模块设计几个Oracle Materialized View，没什么具体的
要求，因为现在的也工作正常，但是觉得不优化（因为数据直接写入table，不是很必
要）
现在想把table中的一些经常需要scheduler去写入的field去掉，抓出来做view。但是
几乎每20秒要IO一次，所以觉得用普通的view不是很好的design。这个view基本是如下
数据表大小做inner join
100*500*3000*30*50
然后aggregation去SUM，还要用到DECODE，加上where 后面四个 and condition
研究Materialized View，现在有一吨问题：
1. 如何判断是否需要QUERY REWRITE
2. 如果想每三分钟refresh一次，这么写有何不妥？
BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
24)) WITH rowid AS
3. 建Materialized View，加不加primary key有什么区别？如果原table的field就有
了index，在MV里就不存在了？
4. 当缺乏大数据库做测试的情况下，使用Materialized View TUNE_MVIEW， DBMS_
MVIEW.EXPLAIN_REWRITE的结果是否值得信赖？
5. 如果不想全视图刷新，那么就要给base的表建log,(新手请别打我）不知这个log建
后对于base表的性能有何影响？
整个design都是以性能优先为准，性能是一切。请指点，谢谢。 | c*****d
发帖数: 6045 | 2 对mview了解不多，一起讨论
1. 如何判断是否需要QUERY REWRITE
[ ] 你是要判断SQL是否REWRITE吗？如果是这个意思，用explain plan或者DBMS_MVIEW
.EXPLAIN_REWRITE就可以看到
2. 如果想每三分钟refresh一次，这么写有何不妥？
BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
24)) WITH rowid AS
[ ] 语法没问题，就是refresh太频繁了，除非你们业务有这个需要，我们是一天
refresh一次，oltp交易流水给DW作分析
3. 建Materialized View，加不加primary key有什么区别？如果原table的field就有
了index，在MV里就不存在了？
[ ] 你理解错了，这个PK不是mview上的PK，用来判断数据变化
是基于table上的PK
mview要么基于PK，要么基于rowid
4. 当缺乏大数据库做测试的情况下，使用Materialized View TUNE_MVIEW， DBMS_
MVIEW.EXPLAIN_REWRITE的结果是否值得信赖？
[ ] 不知道你说的值得信赖是指哪方面
5. 如果不想全视图刷新，那么就要给base的表建log,(新手请别打我）不知这个log建
后对于base表的性能有何影响？
[ ] 影响不大，或者说我们系统里影响不大。Oracle自动维护这个log table MLOG$_
tablename，把base table上的变化记录到这个log，以后sync到mview，sync之后会自
动移除log中的这些记录 | B*****g
发帖数: 34098 | 3 ding
MVIEW
60*
【在 c*****d 的大作中提到】
: 对mview了解不多，一起讨论
: 1. 如何判断是否需要QUERY REWRITE
: [ ] 你是要判断SQL是否REWRITE吗？如果是这个意思，用explain plan或者DBMS_MVIEW
: .EXPLAIN_REWRITE就可以看到
: 2. 如果想每三分钟refresh一次，这么写有何不妥？
: BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
: 24)) WITH rowid AS
: [ ] 语法没问题，就是refresh太频繁了，除非你们业务有这个需要，我们是一天
: refresh一次，oltp交易流水给DW作分析
: 3. 建Materialized View，加不加primary key有什么区别？如果原table的field就有
| c*****d
发帖数: 6045 | 4 针对3
来做个实验就能说明问题了
SQL> create table tlu.emp as select * from dba_objects;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
EMP TABLE
SQL> alter table tlu.emp add constraint pk_emp primary key (object_id);
SQL> select constraint_name,constraint_type,table_name
from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_EMP P EMP
SQL> create index ind_emp on emp(status);
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
-- 到此我们创建了表，一个pk，两个index
SQL> create materialized view log on emp;
Materialized view log created.
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
MLOG$_EMP TABLE
RUPD$_EMP TABLE
SQL> create materialized view mv_emp_1
refresh fast with primary key on demand
as
select object_id,object_type,status from emp;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
MLOG$_EMP TABLE
RUPD$_EMP TABLE
MV_EMP_1 TABLE
PK_EMP1 INDEX
MV_EMP_1 MATERIALIZED VIEW
SQL> select constraint_name,constraint_type,table_name
from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_EMP1 P MV_EMP_1
PK_EMP P EMP
-- 到此可以看到创建了mview with PK，原来表上的index并不会存在于mview
SQL> drop materialized view mv_emp_1;
SQL>drop materialized view log on emp;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
SQL> create materialized view log on emp with rowid;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
MLOG$_EMP TABLE
SQL> create materialized view mv_emp_2 refresh fast with rowid on demand as
select object_id,object_type,status from emp;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
PK_EMP INDEX
EMP TABLE
IND_EMP INDEX
MLOG$_EMP TABLE
MV_EMP_2 TABLE
I_SNAP$_MV_EMP_2 INDEX
MV_EMP_2 MATERIALIZED VIEW
SQL> select constraint_name,constraint_type,table_name
from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_EMP P EMP
-- 到此可以看到创建了mview with rowid，原来表上的PK,index并不会存在于mview | c*****d
发帖数: 6045 | 5 结论
mview和view不同在于view在数据库中是文本
mview是segment
原来表中的索引和mview没有任何关系
可以理解为两个独立的表，只不过会定期同步 |
|
|
|
|
|
|
|
