问题:
物化视图在基表数据修改后未经提交就直接查询速度很慢,之后再提交也没有作用,必须要过一段时间才可恢复,具体见下测试,希望高手指点!
--过程如下
CREATE MATERIALIZED VIEW LOG ON DU_PSUPCASEMX with primary key;
CREATE MATERIALIZED VIEW MV_PSUPPD02
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT * From DU_PSUPCASEMX
WHERE XZCLXL=0 AND PDCODE='02';
analyze table du_psupcasemx compute statistics;
--测试效果
SQL> SELECT count(*) From DU_PSUPCASEMX
2 WHERE XZCLXL=0 AND PDCODE='02';
COUNT(*)
----------
2069
Executed in 0.063 seconds
SQL> update du_psupcasemx set XZCLXL=1234
2 where opseno=69491960 and xzclxl=0 and pdcode='02';
1 row updated
SQL> SELECT count(*) From DU_PSUPCASEMX
2 WHERE XZCLXL=0 AND PDCODE='02';
COUNT(*)
----------
2067
Executed in 37.736 seconds
(这个还可以理解,因为没有提交,在其它事务中查会快)
SQL> commit;
SQL> SELECT count(*) From DU_PSUPCASEMX
2 WHERE XZCLXL=0 AND PDCODE='02';
COUNT(*)
----------
2069
Executed in 34.326 seconds
似乎是修改数据后得commit 后才能查询,直接查询的话,不仅当前速度慢,而且commit后还是慢!
再次查询
SQL> /
COUNT(*)
----------
2129
Executed in 34.85 seconds
尝试1:新开一个连接也一样慢。
Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as siis
SQL>
SQL> select count(*) from DU_PSUPCASEMX
2 WHERE XZCLXL=0 AND PDCODE='02';
COUNT(*)
----------
2129
Executed in 35.85 seconds
尝试2:再次修改数据并提交,一样慢。
SQL> update du_psupcasemx set XZCLXL=0
2 where opseno=48473058 ;
1 row updated
SQL> commit;
Commit complete
SQL>
SQL> select count(*) from SBDU_PSUPCASEMX
2 WHERE XZCLXL=0 AND PDCODE='02';
COUNT(*)
----------
2127
Executed in 36.85 seconds
理解:好像一定要过一段时间(几分钟)才能快起来。
实在没法理解,不知这种情况各位老大遇到没有,怎么解决啊。
这种操作有时也没法避免的啊!