[20250103]物化视图distinct group by.txt

来源：这里教程网时间：2026-03-03 21:13:30 作者：

[20250103]物化视图distinct group by.txt --//生产系统遇到实际上许多条类似语句，顺便拿其中几个出来,真心不知道开发如何学计算机的。 1.问题提出： SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id c29undaquszs6 -- SQL_ID = c29undaquszs6 come from shared pool select distinct ritem from routine2; SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id fhz2xwcnx2uyh -- SQL_ID = fhz2xwcnx2uyh come from shared pool select distinct rtype from routine2; SYS@127.0.0.1:9106/xtdb/xtdb2> @ seg2 nis5.routine2 '' SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ------ ----- ------------ ------------ ------------------- ------ ------ ------ 1088 NIS5 ROUTINE2 TABLE NIS 139264 21 843528 SYS@127.0.0.1:9106/xtdb/xtdb2> @ desczz nis5.routine2 ritem,rtype eXtended describe of nis5.routine2 DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- ------------ ------------ -------------- ---------- --------- ----------- -------------------- ---------------------- NIS5 ROUTINE2 4861544 2025-01-01 22:01:25 7 RTYPE VARCHAR2(40) 36 .00000010285 0 FREQUENCY 36 13种呼吸道病原体检测真菌三项病原体核酸检测 4861544 2025-01-01 22:01:25 8 RITEM VARCHAR2(40) 32 .00000010285 0 FREQUENCY 32 13种呼吸道病原体检测中性粒细胞 --//表ROUTINE2 1088M，而RTYPE，RITEM的不同值分布为36，32。要优化上面的sql语句分布建立对应索引简直就是资源浪费。 --//而且类似的sql语句还有一大堆,不想贴出来了，这样的项目简直就是豆腐渣中的豆腐渣工程。 --//上该公司网站，我真心不知道这样的产品竟然到处在买，简直就是垃圾，站在优化的角度又是一个豆腐渣工程。 --//要优化它，只能建立索引，还好NUM_NULLS＝0,要给字段加not null约束，然后建立相应索引。 --//执行计划可以猜测出来走快速全索引扫描(需要另外排序)，或者走全索引扫描(不需要排序). --//我在想实际上每个值仅仅返回1行，是否可以通过递归实现类似功能，这样可以减少逻辑读，规避快速全索引扫描或者全索引扫描带 --//来的逻辑读，这样表索引应该也不小，即使选择压缩模式。 --//通过例子验证我的想法。 2.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t as select * from all_objects; create index i_t_owner on t(owner) COMPRESS 1; --//alter table t modify owner not null;这步不需要。 --//分析略。 SCOTT@book01p> create unique index pk_t on t (object_id); Index created. SCOTT@book01p> alter table t add constraint pk_t primary key (object_id); Table altered. 3.采用物化视图看看是否可行： variable t varchar2(50) begin dbms_advisor.tune_mview(task_name=>:t, mv_create_stmt=>'create materialized view t_mv refresh fast on commit as select distinct owner from t'); end; / begin * ERROR at line 1: ORA-13600: error encountered in Advisor QSM-03112: Invalid CREATE MATERIALIZED VIEW statement ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 229 ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 461 ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1150 ORA-06512: at "SYS.DBMS_ADVISOR", line 858 ORA-06512: at line 2 --//根本不可能使用refresh fast on commit 方式。 variable t varchar2(50) begin dbms_advisor.tune_mview(task_name=>:t, mv_create_stmt=>'create materialized view t_mv refresh fast on commit as select owner,count(*) owner_count from t group by owner'); end; / SCOTT@book01p> column statement format a140 SCOTT@book01p> 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"."T" WITH ROWID, SEQUENCE ("OWNER") INCLUDING NEW VALUES IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."T" ADD ROWID, SEQUENCE ("OWNER") INCLUDING NEW VALUES IMPLEMENTATION CREATE MATERIALIZED VIEW "SCOTT"."T_MV" REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT "SCOTT"."T"."OWNER" C1, COUNT(*) M1 FROM " SCOTT"."T" GROUP BY "SCOTT"."T"."OWNER" UNDO DROP MATERIALIZED VIEW "SCOTT"."T_MV" 4 rows selected. --//可以发现建议要这样建立MATERIALIZED VIEW LOG. --//exec dbms_advisor.delete_task(:t); --//获取执行脚本: CREATE MATERIALIZED VIEW LOG ON "SCOTT"."T" WITH ROWID, SEQUENCE ("OWNER") INCLUDING NEW VALUES --//ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."T" ADD ROWID, SEQUENCE ("OWNER") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW "SCOTT"."T_MV" REFRESH FAST WITH ROWID enable QUERY REWRITE AS SELECT "SCOTT"."T"."OWNER" C1, COUNT(*) M1 FROM " SCOTT"."T" GROUP BY "SCOTT"."T"."OWNER" --//注意修改DISABLE->enanle。并且加上on commit。 SCOTT@book01p> exec dbms_advisor.delete_task(:t); PL/SQL procedure successfully completed. SCOTT@book01p> CREATE MATERIALIZED VIEW LOG ON "SCOTT"."T" WITH ROWID, SEQUENCE ("OWNER") INCLUDING NEW VALUES ; Materialized view log created. SCOTT@book01p> CREATE MATERIALIZED VIEW "SCOTT"."T_MV" REFRESH FAST on commit WITH ROWID enable QUERY REWRITE AS SELECT "SCOTT"."T"."OWNER" C1, COUNT(*) M1 FROM "SCOTT"."T" GROUP BY "SCOTT"."T"."OWNER"; Materialized view created. 4.测试看看： --//distinct肯定不行，测试略。 COTT@book01p> select owner from t group by owner; OWNER ------------------------------ APPQOSSYS AUDSYS CTXSYS DBSFWUSER DBSNMP DVF DVSYS GSMADMIN_INTERNAL LBACSYS MDSYS OJVMSYS OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN PUBLIC REMOTE_SCHEDULER_AGENT SCOTT SI_INFORMTN_SCHEMA SYS SYSTEM WMSYS XDB 25 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID fcr9kfw6mn8kb, child number 0 ------------------------------------- select owner from t group by owner Plan hash value: 139569370 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 25 |00:00:00.01 | 3 | | 1 | MAT_VIEW REWRITE ACCESS FULL| T_MV | 1 | 25 | 225 | 3 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1A6C0E16 / "T_MV"@"SEL$21A6E33A" 18 rows selected. --//可以发现使用物化视图。 SCOTT@book01p> update t set owner = 'AAAA' where object_id=2; 1 row updated. SCOTT@book01p> commit ; Commit complete. SCOTT@book01p> select Owner from t group by owner; OWNER ------------------------------ APPQOSSYS AUDSYS CTXSYS DBSFWUSER DBSNMP DVF DVSYS GSMADMIN_INTERNAL LBACSYS MDSYS OJVMSYS OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN PUBLIC REMOTE_SCHEDULER_AGENT SCOTT SI_INFORMTN_SCHEMA SYS SYSTEM WMSYS XDB AAAA 26 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dgyvkzyvg1g3z, child number 0 ------------------------------------- select Owner from t group by owner Plan hash value: 139569370 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 26 |00:00:00.01 | 7 | | 1 | MAT_VIEW REWRITE ACCESS FULL| T_MV | 1 | 25 | 225 | 3 (0)| 00:00:01 | 26 |00:00:00.01 | 7 | ------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1A6C0E16 / "T_MV"@"SEL$21A6E33A" 18 rows selected. --//提交后可以使用物化视图。 5.测试不提交的情况： SCOTT@book01p> update t set owner = 'SYS' where object_id=2; 1 row updated. SCOTT@book01p> select Owner from t group by owner; ... SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dgyvkzyvg1g3z, child number 1 ------------------------------------- select Owner from t group by owner Plan hash value: 3243201395 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 35 (100)| | 25 |00:00:00.03 | 116 | | 1 | HASH GROUP BY | | 1 | 25 | 125 | 35 (12)| 00:00:01 | 25 |00:00:00.03 | 116 | | 2 | INDEX FAST FULL SCAN| I_T_OWNER | 1 | 70025 | 341K| 32 (4)| 00:00:01 | 70025 |00:00:00.01 | 116 | ----------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / "T"@"SEL$1" --//产生新光标，无法使用物化视图表。 --//打开新的会话，查询： SCOTT@book01p> select Owner from t group by owner; SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dgyvkzyvg1g3z, child number 2 ------------------------------------- select Owner from t group by owner Plan hash value: 139569370 -------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | MAT_VIEW REWRITE ACCESS FULL| T_MV | 25 | 225 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1A6C0E16 / "T_MV"@"SEL$21A6E33A" --//可以使用物化视图。 --//这是采用物化视图的一个缺陷，而且提交后该会话查询不会使用物化视图,大家可以自行测试，不过多数情况可以避免，因为应用基 --//本都是提交后查询。 6.顺便测试19c的新功能实时物化视图： SCOTT@book01p> drop MATERIALIZED VIEW "SCOTT"."T_MV" ; Materialized view dropped. CREATE MATERIALIZED VIEW "SCOTT"."T_MV" REFRESH FAST on demand WITH ROWID start with sysdate next sysdate + interval '1' minute enable on query computation enable QUERY REWRITE AS SELECT "SCOTT"."T"."OWNER" C1, COUNT(*) M1 FROM "SCOTT"."T" GROUP BY "SCOTT"."T"."OWNER"; --//建立物化视图按需刷新，间隔1分钟刷新1次。 --//特点就是没有更新物化视图，查询也可以使用，但是执行计划很复杂。 SCOTT@book01p> update t set owner = 'BBB' where object_id=2; 1 row updated. SCOTT@book01p> commit; Commit complete. select Owner from t group by owner SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dgyvkzyvg1g3z, child number 1 ------------------------------------- select Owner from t group by owner Plan hash value: 844669193 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | | | 17 (100)| | 26 |00:00:00.01 | 28 | | | | | 1 | VIEW | | 1 | 53 | 3498 | 17 (18)| 00:00:01 | 26 |00:00:00.01 | 28 | | | | | 2 | UNION-ALL | | 1 | | | | | 26 |00:00:00.01 | 28 | | | | |* 3 | VIEW | VW_FOJ_0 | 1 | 26 | 1794 | 7 (15)| 00:00:01 | 25 |00:00:00.01 | 10 | | | | |* 4 | HASH JOIN FULL OUTER | | 1 | 26 | 2002 | 7 (15)| 00:00:01 | 27 |00:00:00.01 | 10 | 1856K| 1856K| 544K (0)| | 5 | VIEW | | 1 | 2 | 142 | 4 (25)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | | 6 | HASH GROUP BY | | 1 | 2 | 154 | 4 (25)| 00:00:01 | 2 |00:00:00.01 | 7 | 879K| 879K| | |* 7 | TABLE ACCESS FULL | MLOG$_T | 1 | 2 | 154 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | | 8 | VIEW | | 1 | 26 | 156 | 3 (0)| 00:00:01 | 26 |00:00:00.01 | 3 | | | | | 9 | MAT_VIEW ACCESS FULL | T_MV | 1 | 26 | 338 | 3 (0)| 00:00:01 | 26 |00:00:00.01 | 3 | | | | | 10 | VIEW | | 1 | 27 | 1782 | 10 (20)| 00:00:01 | 1 |00:00:00.01 | 18 | | | | | 11 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 18 | | | | |* 12 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 8 | | | | | 13 | NESTED LOOPS OUTER | | 1 | 26 | 2392 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 8 | | | | | 14 | VIEW | | 1 | 2 | 158 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 7 | | | | |* 15 | HASH GROUP BY | | 1 | 2 | 154 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 7 | 879K| 879K| | |* 16 | TABLE ACCESS FULL | MLOG$_T | 1 | 2 | 154 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | |* 17 | INDEX UNIQUE SCAN | I_SNAP$_T_MV | 1 | 13 | 169 | 0 (0)| | 0 |00:00:00.01 | 1 | | | | | 18 | NESTED LOOPS | | 1 | 1 | 95 | 6 (17)| 00:00:01 | 0 |00:00:00.01 | 10 | | | | | 19 | VIEW | | 1 | 2 | 164 | 4 (25)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | | 20 | HASH GROUP BY | | 1 | 2 | 154 | 4 (25)| 00:00:01 | 2 |00:00:00.01 | 7 | 879K| 879K| | |* 21 | TABLE ACCESS FULL | MLOG$_T | 1 | 2 | 154 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | |* 22 | MAT_VIEW ACCESS BY INDEX ROWID| T_MV | 2 | 1 | 13 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 3 | | | | |* 23 | INDEX UNIQUE SCAN | I_SNAP$_T_MV | 2 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//等上1分钟执行： select Owner from t group by owner SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dgyvkzyvg1g3z, child number 0 ------------------------------------- select Owner from t group by owner Plan hash value: 139569370 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 26 |00:00:00.01 | 7 | | 1 | MAT_VIEW REWRITE ACCESS FULL| T_MV | 1 | 26 | 234 | 3 (0)| 00:00:01 | 26 |00:00:00.01 | 7 | ------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1A6C0E16 / "T_MV"@"SEL$21A6E33A"

编辑推荐：

返回列表

[20250103]物化视图distinct group by.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推