[20230124]测试是否能使用物化视图优化.txt

来源：这里教程网时间：2026-03-03 18:20:38 作者：

[20230124]测试是否能使用物化视图优化.txt --//生产系统有如下sql语句: SYS@127.0.0.1:9014/ywdb> @ sql_id 77ygdvhd1y3rk --SQL_ID = 77ygdvhd1y3rk select ylxh from l_lis_sqdmx where prehyid =:1 and rownum =:"SYS_B_0" ; --//有一个限制条件。rownum =:"SYS_B_0" ，很明显参数是1. SYS@127.0.0.1:9014/ywdb> @ descz l_lis_sqdmx column_name='PREHYID' Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------- ---------- PORTAL_HIS L_LIS_SQDMX 2713377 2022-04-21 09:19:23 3 PREHYID NOT NULL NUMBER(6,0) 442 .00226244344 0 1 370 95648 --//仅仅442个不同值。表记录2713377。建立索引应该不好啊. --//我当时觉得应该有一个应用的数据字典记录这些信息，这样就不需要这样这样查询。说句真心话，为了这条语句建立这样的索引纯粹 --//是资源浪费。实际上查询的本意是监测这个项目是否开展,有个疑问一些项目都有第1次开展. --//实际上平时的查询并不慢，即使执行计划是全表扫描,因为存在1个限制条件,rownum =:"SYS_B_0" .导致许多项目PREHYID在前面的几 --//个数据块扫描都能很快定位，但是如果新开展的PREHYID项目，比如核酸检查，问题就来了,因为这个项目最近几年才增加的项目。要 --//扫描好几个G的数据量才能定位prehyid =:1 and rownum =1 的记录。开发写代码随心所欲,根本没考虑长久运行!! --//不知道使用物化视图是否可能解决这个问题，从来没有在生产系统做过这样的优化，尝试看看。 --//很久不做这类测试,参考链接:connor-mcdonald.com/2022/01/31/silent-data-corruption-constraints-spanning-multiple-rows/ --//[20120808]学习物化视图.txt --//http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/ --//[20120809]学习物化视图(补充).txt 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> create sequence seq2 cache 1000; Sequence created. SCOTT@test01p> create table empx as select seq2.nextval empno_pk,emp.empno,emp.ename,dept.deptno,dept.dname from emp,dept,(select 1 from dual connect by level<=100) where dept.deptno = emp.deptno; Table created. SCOTT@test01p> create unique index scott.pk_empx on scott.empx (empno_pk); Index created. SCOTT@test01p> alter table scott.empx add constraint pk_empx primary key (empno_pk); Table altered. SCOTT@test01p> @ gts empx Gather Table Statistics for table empx... exec dbms_stats.gather_table_stats(null, upper('empx'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) PL/SQL procedure successfully completed. 2.尝试建立物化视图: --//使用dbms_advisor包可以获得一些建议方法. variable t varchar2(50) begin dbms_advisor.tune_mview(task_name=>:t, mv_create_stmt=>'create materialized view empx_mv refresh fast on commit as select deptno,dname,count(*) from empx group by deptno,dname'); end; / SCOTT@test01p> column statement format a140 SCOTT@test01p> select script_type,statement from user_tune_mview where task_name=:t order by action_id; SCRIPT_TYPE STATEMENT -------------- -------------------------------------------------------------------------------------------------------------------------------------------- IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "SCOTT"."EMPX" WITH ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."EMPX" ADD ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES IMPLEMENTATION CREATE MATERIALIZED VIEW SCOTT.EMPX_MV REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SCOTT.EMPX.DNAME C1, SCOTT.EMPX.DEPTNO C2, COUNT(*) M1 FROM SCOTT.EMPX GROUP BY SCOTT.EMPX.DNAME, SCOTT.EMPX.DEPTNO UNDO DROP MATERIALIZED VIEW SCOTT.EMPX_MV --//可以发现建议要这样建立MATERIALIZED VIEW LOG. --//exec dbms_advisor.delete_task(:t); --//获取执行脚本: CREATE MATERIALIZED VIEW LOG ON EMPX WITH ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW SCOTT.EMPX_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT EMPX.DNAME C1, EMPX.DEPTNO C2, COUNT(*) M1 FROM EMPX GROUP BY EMPX.DNAME, EMPX.DEPTNO; --//执行如下,注意要把前面disable换成enable. SCOTT@test01p> CREATE MATERIALIZED VIEW LOG ON EMPX WITH ROWID, SEQUENCE ("DEPTNO","DNAME") INCLUDING NEW VALUES; Materialized view log created. SCOTT@test01p> CREATE MATERIALIZED VIEW SCOTT.EMPX_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT EMPX.DNAME C1, EMPX.DEPTNO C2, COUNT(*) M1 FROM EMPX GROUP BY EMPX.DNAME, EMPX.DEPTNO; Materialized view created. SCOTT@test01p> exec dbms_advisor.delete_task(:t); PL/SQL procedure successfully completed. SCOTT@test01p> select script_type,statement from user_tune_mview where task_name=:t order by action_id; no rows selected 3.测试: SCOTT@test01p> select dname from empx where deptno = 20 group by dname; DNAME -------------------- RESEARCH SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 9p0y7b0r7fqm6, child number 0 ------------------------------------- select dname from empx where deptno = 20 group by dname Plan hash value: 1996975425 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 12 | 4 (25)| 00:00:01 | 1818K| 1818K| 457K (0)| |* 2 | MAT_VIEW REWRITE ACCESS FULL| EMPX_MV | 1 | 12 | 3 (0)| 00:00:01 | | | | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$DFCE5C0F 2 - SEL$DFCE5C0F / EMPX_MV@SEL$6036B53D Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMPX_MV"."C2"=20) --//这样执行可以使用物化视图. SCOTT@test01p> select dname from empx where deptno = 10 and rownum=1; DNAME -------------------- ACCOUNTING Plan hash value: 3998239372 ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | COUNT STOPKEY | | | | | | |* 2 | TABLE ACCESS FULL| EMPX | 2 | 24 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- --//这样执行不行!!必须在后面加入group by dname; SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> select dname,count(*) from empx where deptno = 10 and rownum=1 group by dname; DNAME COUNT(*) -------------------- ---------- ACCOUNTING 300 Plan hash value: 1244312600 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 9 | | | | | 1 | HASH GROUP BY | | 1 | 1 | 15 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 9 | 1116K| 1116K| 507K (0)| |* 2 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 9 | | | | |* 3 | MAT_VIEW REWRITE ACCESS FULL| EMPX_MV | 1 | 1 | 15 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 9 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- --//如果设置query_rewrite_enabled=false: SCOTT@test01p> alter session set query_rewrite_enabled=false; Session altered. SCOTT@test01p> select dname,count(*) from empx where deptno = 10 and rownum=1 group by dname; DNAME COUNT(*) -------------------- ---------- ACCOUNTING 1 --//不知道这个是否算bug,两个结果集不一致. 4.存在DML测试: --//修改为提交时更新,这样刷新时有点慢,但是比较合适实时查询. drop MATERIALIZED VIEW SCOTT.EMPX_MV; CREATE MATERIALIZED VIEW SCOTT.EMPX_MV REFRESH FAST on commit WITH ROWID ENABLE QUERY REWRITE AS SELECT SCOTT.EMPX.DNAME C1, SCOTT.EMPX.DEPTNO C2, COUNT(*) M1 FROM SCOTT.EMPX GROUP BY SCOTT.EMPX.DNAME, SCOTT.EMPX.DEPTNO; SCOTT@test01p> insert into empx values (9001,9001,'Test1',40,'XXXX'); 1 row created. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select dname from empx where deptno = 40 and rownum=1 group by dname; DNAME -------------------- XXXX Plan hash value: 1244312600 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 11 | 4 (25)| 00:00:01 | 2170K| 2170K| 496K (0)| |* 2 | COUNT STOPKEY | | | | | | | | | |* 3 | MAT_VIEW REWRITE ACCESS FULL| EMPX_MV | 1 | 11 | 3 (0)| 00:00:01 | | | | ---------------------------------------------------------------------------------------------------------------------- --//ok,提交后ok. --//如果存在事务呢? --//session 1,不提交: SCOTT@test01p> delete from empx where empno=9001; 1 row deleted. --//session 2: SCOTT@test01p> select dname from empx where deptno = 40 and rownum=1 group by dname; DNAME -------------------- XXXX Plan hash value: 1244312600 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 7 | | | | | 1 | HASH GROUP BY | | 1 | 1 | 11 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 7 | 2170K| 2170K| 502K (0)| |* 2 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 7 | | | | |* 3 | MAT_VIEW REWRITE ACCESS FULL| EMPX_MV | 1 | 1 | 11 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- --//可以发现其它会话依旧可以通过物化视图查询. --//session 1: SCOTT@test01p> select dname from empx where deptno = 10 and rownum=1 group by dname; DNAME -------------------- ACCOUNTING Plan hash value: 1704460098 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 12 | 3 (34)| 00:00:01 | 1698K| 1698K| 495K (0)| |* 2 | COUNT STOPKEY | | | | | | | | | |* 3 | TABLE ACCESS FULL| EMPX | 2 | 24 | 2 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- --//可以发现当前会话无法使用物化视图. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select dname from empx where deptno = 10 and rownum=1 group by dname; DNAME -------------------- ACCOUNTING Plan hash value: 1704460098 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 12 | 3 (34)| 00:00:01 | 1698K| 1698K| 507K (0)| |* 2 | COUNT STOPKEY | | | | | | | | | |* 3 | TABLE ACCESS FULL| EMPX | 2 | 24 | 2 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- --//出现这样的情况这样当前会话执行select dname from empx where deptno = 10 and rownum=1 group by dname;都是无法使用 --//物化视图查询.一旦出现这样的情况新登陆的会话,查询deptno = 10的情况,都是无法使用物化事务. --//我使用文字变量如果换成绑定变量,如果出现上面的情况,会导致后面的执行无论那个session都无法使用物化视图的情况.大家可以自 --//行测试. variable b number; exec :b := 20; select dname from empx where deptno = :b and rownum=1 group by dname; --//如果你不存在执行dml后,没有提交,使用类似查询,就会出现无法使用物化视图的情况. --//当然如果不同的语句sql_id不同.还是可以使用物化视图. 4.建议: --//我这样修改要修改代码,我个人建议也许最佳方式是直接查询物化视图对应的表.可以在C2字段上建立索引查询更快. --//实际上最佳的方式应用应该存在一个应用的数据字典记录这些信息.使用一个字段status表示是否开展就ok了. --//而不是在查询时选择业务表完成这样的操作. SCOTT@test01p> select * from empx_mv; C1 C2 M1 -------------------- -------- ---------- RESEARCH 20 500 SALES 30 600 ACCOUNTING 10 300 XXXX 40 1 SCOTT@test01p> insert into empx values (9002,9002,'Test2',10,'ACCOUNTING'); 1 row created. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select * from empx_mv; C1 C2 M1 -------------------- -------- ---------- RESEARCH 20 500 SALES 30 600 ACCOUNTING 10 301 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ XXXX 40 1 --//C1='ACCOUNTING',增加1条记录.