Archive for the ‘mv’ tag
可更新分区物化视图Issue
这个限制其实按照道理不应该存在,但是确实有这个问题,一个可更新的分区物化视图无法刷新会引起分区间记录转移的改动。这句话比较拗口,那么用例子说明一下。
创建一个分区表,注意要enable row movement,这样后面才能去更新分区键的字段值。
- CREATE TABLE PARTITION_BY_RANGE
- ( FIRST_NAME VARCHAR2(10),
- MIDDLE_INIT VARCHAR2(1),
- LAST_NAME VARCHAR2(10),
- BIRTH_MM INT NOT NULL,
- BIRTH_DD INT NOT NULL,
- BIRTH_YYYY INT NOT NULL)
- PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
- (PARTITION DOBS_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
- PARTITION DOBS_IN_1972 VALUES LESS THAN (1973, 01 ,01),
- PARTITION DOBS_IN_1973 VALUES LESS THAN (1974, 01 ,01),
- PARTITION DOBS_IN_1974 VALUES LESS THAN (1975, 01 ,01),
- PARTITION DOBS_IN_1975_OR_LATER VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
- ENABLE ROW MOVEMENT;
插入分区数据。
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1969', 'A', 'SMITH_1969', 09, 20, 1969);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1970', 'A', 'SMITH_1970', 09, 20, 1970);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1971', 'A', 'SMITH_1971', 09, 20, 1971);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1972', 'A', 'SMITH_1972', 09, 20, 1972);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1973', 'A', 'SMITH_1973', 09, 20, 1973);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1974', 'A', 'SMITH_1974', 09, 20, 1974);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1975', 'A', 'SMITH_1975', 09, 20, 1975);
- INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1976', 'A', 'SMITH_1976', 09, 20, 1976);
- COMMIT;
创建物化视图日志。
- create MATERIALIZED VIEW LOG ON PARTITION_BY_RANGE WITH PRIMARY KEY;
创建分区可更新物化视图。不是分区视图不会有问题,不是可更新视图也不会问题。
- create MATERIALIZED VIEW mv_pr
- PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
- (PARTITION MV_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
- PARTITION MV_IN_1972 VALUES LESS THAN (1973, 01 ,01),
- PARTITION MV_IN_1973 VALUES LESS THAN (1974, 01 ,01),
- PARTITION MV_IN_1974 VALUES LESS THAN (1975, 01 ,01),
- PARTITION MV_IN_1975_OR_LATER VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
- refresh fast with primary key for update
- as
- select * from PARTITION_BY_RANGE;
随便修改基表的几条数据,目的是引起分区记录的转移,比如将DOBS_IN_1972分区内的记录全部更新到DOBS_IN_1973中。
然后,尝试做物化视图的手动刷新,将会报错。
- SQL> exec dbms_mview.refresh(list => 'MV_PR');
- begin dbms_mview.refresh(list => 'MV_PR'); end;
- ORA-12008: error in materialized view refresh path
- ORA-14402: updating partition key column would cause a partition change
- ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
- ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
- ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
- ORA-06512: at line 2
目前没有什么可以解决的方法,只能是修改为非分区的或者只读的物化视图。
探讨实体化视图的刷新机制
今天给客户搭建历史查询服务器,用oracle8i的snapshot实现,也就是9i的实体化视图。
顺手做了一下trace,看了一下刷新时候oracle后台是怎么工作的。
前期准备,使用DBMS_SUPPORT包,这个包默认是没有安装的,需要运行下面的命令来手动安装。
- SQL>conn / as sysdba
- SQL>@?\rdbms\admin\dbmssupp.sql
- SQL>GRANT execute ON dbms_support TO kamus;
- SQL>CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
先看快速刷新,测试表是T1,创建了快照日志,用于刷新的视图是MV_T1,用户是KAMUS
执行trace:
- SQL>conn kamus
- SQL>exec dbms_support.start_trace(waits=>TRUE,binds=>TRUE);
- SQL>exec dbms_mview.refresh(list => 'MV_T1');
- SQL>exec dbms_support.stop_trace;
然后tkprof生成trace结果的报表,下面只是节选了其中一部分。
执行一次dbms_mview.refresh,Oracle后台会执行13个 user SQL和92个 internal SQL,实在是一个繁杂的工作。
1。开始刷新
- BEGIN dbms_mview.refresh(list => 'MV_T1'); END;
2。检查SNAP$表,确认当前用户是否有需要刷新的视图
3。在DBMS_LOCK_ALLOCATED数据字典中更新记录,设置过期时间
- UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400)
- WHERE
- ROWID = :B2
4。检查可能会用到的dblink和一些高级队列的数据字典
5。检查表的相关约束
6。检查几个初始化参数的值,包括_enable_refresh_schedule,_delay_index_maintain,compatible
7。将MLOG中所有没有标志为定时刷新的记录更新为立刻刷新
- update "KAMUS"."MLOG$_T1" set snaptime$$ = :1
- where
- snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
8。重新编译MV_T1实体化视图
- ALTER SUMMARY "KAMUS"."MV_T1" COMPILE
这一步比较可疑,SQL中是没有alter summary找个命令的,如果是编译的话,那么就可能锁定对象,就有可能产生library cache lock
9。检查要执行的SQL文,这一步比较有趣
- SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum
- FROM
- sys.snap_refop$ WHERE ((operation# >= 0 AND operation# <= 6) OR operation#
- IN (10, 12, 13)) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER
- BY tabnum, setnum, operation#
对于一个MV刷新将会使用到SQL全部存在这张表中。
如果是fast刷新,那么对于查询mlog表,查询基表的数据,insert、update、delete实体化视图都分别有一句SQL。
其中operation#字段值的常见含义如下:
0:查询mlog表
1:对于实体化视图的delete操作
2:查询基表的最新数据
3:对于实体化视图的update操作
4:对于实体化视图的insert操作
如果是complete刷新,那么只有一条记录,是基于基表的全表insert操作,operation#是7。
此处的执行计划显示是对于snap_refop$的全表扫描,如果系统中存在大量需要refresh的实体化视图,无疑是影响性能的。
10。取得需要更新的记录主键
- SELECT DISTINCT LOG$."IDATE"
- FROM
- (SELECT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" > :1
- AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."IDATE") NOT IN (SELECT
- MAS_TAB$."IDATE" FROM "T1" "MAS_TAB$" WHERE LOG$."IDATE" = MAS_TAB$."IDATE")
注意到这里使用了distinct,也就是我们可以猜测,如果在一次刷新之前对于同一条记录作了多次的修改,那么刷新操作只需要作一次,就是获得基表中该条记录的最新值就可以了。
IDATE字段是我的测试表中的主键。
“DMLTYPE$$” != ‘I’表示不是insert的操作。
此处的执行计划显示对于mlog表进行了一次全表扫描,如果有大量的更新操作,无疑又是影响性能的一步。
11。取得基表中当前需要刷新的记录所有字段的最新值
- SELECT CURRENT$."IDATE",CURRENT$."C"
- FROM
- (SELECT "T1"."IDATE" "IDATE","T1"."C" "C" FROM "T1" "T1") CURRENT$, (SELECT
- DISTINCT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" >
- :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."IDATE" = LOG$."IDATE"
这一步操作表示,mlog中只存储修改操作涉及到的记录主键,其它的字段值仍然会到基表中去作查询。
此处的执行计划显示对于mlog表再一次作了全表扫描。
12。用取得的最新值更新实体化视图
- UPDATE "KAMUS"."MV_T1" SET "IDATE" = :1,"C" = :2
- WHERE
- "IDATE" = :1
这一步仍然比较奇怪,因为我的测试中只作了insert,并没有update的操作,莫非oracle在刷新时,并不管是否存在update的操作,都会例行作一次视图数据的更新?不过此处更新会使用实体化视图中的主键,速度应该时很快的。
13。将取得的最新值插入到实体化视图中
- INSERT INTO "KAMUS"."MV_T1" ("IDATE","C")
- VALUES
- (:1,:2)
这步才到了真正要实现的目的上,呵呵。
14。更新一批数据字典,表明刷新已经完成
15。删除mlog表中已经刷新过的记录
- delete from "KAMUS"."MLOG$_T1"
- where
- snaptime$$ <= :1
这一步操作是比较耗费资源的,使用delete,产生redo和undo,无法降低mlog表的HWM标志,同时又是一次全表扫描,如果经常有大量更新发生,最好能定时作mlog表的truncate动作,否则这一步操作可能会越来越慢。
至此,一次实体化视图的快速刷新算是完全结束了。
我们继续看一下完全刷新的后台机制。
1-8步跟快速刷新基本相同。
9。检查要执行的SQL文
- SELECT operation#, cols, sql_txt
- FROM
- sys.snap_refop$ WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND
- instsite = :3
可以看到直接去找operation# = 7的SQL了,这就是完全刷新需要使用的SQL。
10。检查完全刷新涉及到的约束,索引,触发器
11。删除实体化视图中的原有数据
- delete from "KAMUS"."MV_T"
这一步让我很诧异,记得文档中说应该是truncate操作,但是此处显示的是delete?这样的话,完全刷新的代价实在是很大了。
12。插入基表中所有数据
- INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "KAMUS"."MV_T"("X") SELECT "T"."X"
- FROM "T" "T"
这里使用到的提示/*+ BYPASS_RECURSIVE_CHECK */,是不是在实际应用中可以提高INSERT的效率呢?
13。更新一批数据字典,表明刷新已经完成
14。如果在基表上创建了刷新日志mlog表,那么Oracle不管这次刷新是不是完全刷新,都会去作一次删除mlog表中数据的操作。如果没有创建过mlog,那么这一步将被省略。所以如果决定使用完全刷新,那么就不要在基表上创建刷新日志了,省得无谓的资源消耗。
最简单的创建复制环境中只读实体化视图站点的方法
目的:创建一个实体化视图站点,定时刷新,获取主站点中指定表的变化,这个实体化站点可以作为查询服务器使用
步骤:
1。主站点上创建实体化视图日志表
- CREATE MATERIALIZED VIEW LOG ON kamus.account2004;
2。实体化视图站点上创建公用数据库链接
- conn system/password
- CREATE PUBLIC DATABASE LINK orcl using 'ORCL';
3。实体化视图站点上创建刷新组,本例中3分钟刷新一次
- conn system/password
- BEGIN
- DBMS_REFRESH.MAKE (
- name => 'kamus.test_repg',
- list => '',
- next_date => SYSDATE,
- interval => 'SYSDATE + 3/(24*60)',
- implicit_destroy => FALSE,
- rollback_seg => '',
- push_deferred_rpc => TRUE,
- refresh_after_errors => FALSE);
- END;
- /
- COMMIT;
注意:此处必须commit,否则创建的刷新组并没有真正生效。
4。实体化视图站点上创建用户私有数据库链接
- conn kamus/password
- CREATE DATABASE LINK orcl CONNECT TO kamus IDENTIFIED BY password;
5。实体化视图站点上创建实体化视图
- conn kamus/password
- CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM KAMUS.ACCOUNT2004@orcl;
6。实体化视图站点上将创建的视图加入刷新组
- conn kamus/password
- exec DBMS_REFRESH.ADD(name => 'kamus.TEST_REPG', list => 'kamus.ACCOUNT2004', lax => TRUE);
7。测试,在主站点中更新ACCOUNT2004表,过3分钟检查实体化试图站点中的视图,发现更新已经复制成功。
以上为建立只读实体化视图站点的最简单方法:
不需要创建其它的任何用户,比如复制环境中需要的传播者,刷新者,接收者等等
不需要创建任何复制组
不需要生成任何复制对象,不需要生成任何对象的复制支持
![Chanel [K]](http://www.dbform.com/wp-content/chanelk.png)