可更新分区物化视图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

目前没有什么可以解决的方法,只能是修改为非分区的或者只读的物化视图。

4 Comments Add yours

  1. leeecho says:

    好像对基表的truncate分区,也不能刷新,不知道是不是哪里有设置,可以支持truncate分区?

  2. kamus says:

    truncate本来就不支持刷新的,因为这是DDL操作,不会被记录在mvlog中

  3. wangwang says:

    这个提醒好,以后遇见了此类错误也有个心理准备。

  4. ремонт холодильников says:

    ремонт холодильников
    http://www.service-home.ru/

Leave a Reply to leeecho Cancel reply

Your email address will not be published. Required fields are marked *