[20200117]push_pred distinct group by.txt --//优化生产系统一条sql语句，遇到一些问题，视图里面定义使用group by,我发现无法推入。 --//由于生产系统语句太复杂，我做了一个简单的测试例子： 1.环境： 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 t1 as select rownum id1 ,rownum id2,lpad('t1',100,'t1') vc from dual connect by level<=1e5; create table t2 as select rownum id1 ,rownum id2,lpad('t2',100,'t2') vc from dual connect by level<=1e5; create table t3 as select rownum id1 ,rownum id2,lpad('t3',100,'t3') vc from dual connect by level<=1e5; create table t4 as select rownum id1 ,rownum id2,lpad('t4',100,'t4') vc from dual connect by level<=1e5; create view v_t12 as select t1.id2 id2_t1,t2.id2 id2_t2 ,t1.vc vc_t1, t2.vc vc_t2 from t1 ,t2 where t1.id1=t2.id1 group by t1.id2 ,t2.id2 ,t1.vc , t2.vc ; create view v_t34 as select t3.id1 id1_t3,t3.id2 id2_t3,t3.vc vc_t3,t4.id2 id2_t4,t4.vc vc_t4 from t3,t4 where t3.id1=t4.id1; create view v_t12x as select * from v_t12; create view v_t12y as select distinct t1.id2 id2_t1,t2.id2 id2_t2 ,t1.vc vc_t1, t2.vc vc_t2 from t1 ,t2 where t1.id1=t2.id1 ; create index i_t1_id1 on t2(id1); create index i_t2_id1 on t2(id1); create index i_t3_id1 on t3(id1); create index i_t4_id1 on t4(id1); create index i_t1_id2 on t1(id2); --//^_^，字段定义看着有点绕。 2.测试： --//SCOTT@book> alter session set "_bloom_filter_enabled"=false ; --//Session altered. SCOTT@book> select /*+ no_merge(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42; VC_T2 ---------------------------------------------------------------------------------------------------- t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2 --//生产语句实在太复杂，我不加no_merge提示无法模拟出来。 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 6wq8c34x3gx47, child number 0 ------------------------------------- select /*+ no_merge(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42 Plan hash value: 3080093223 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 6883 (100)| | | | | |* 1 | HASH JOIN | | 1 | 80 | | 6883 (1)| 00:01:23 | 2440K| 2440K| 770K (0)| | 2 | MERGE JOIN CARTESIAN | | 1 | 15 | | 3 (0)| 00:00:01 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 10 | | 2 (0)| 00:00:01 | | | | |* 4 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | | 1 (0)| 00:00:01 | | | | | 5 | BUFFER SORT | | 1 | 5 | | 1 (0)| 00:00:01 | 2048 | 2048 | 2048 (0)| |* 6 | INDEX RANGE SCAN | I_T4_ID1 | 1 | 5 | | 1 (0)| 00:00:01 | | | | | 7 | VIEW | V_T12X | 100K| 6347K| | 6880 (1)| 00:01:23 | | | | | 8 | HASH GROUP BY | | 100K| 21M| 22M| 6880 (1)| 00:01:23 | 26M| 4058K| 26M (0)| |* 9 | HASH JOIN | | 100K| 21M| 11M| 2061 (1)| 00:00:25 | 15M| 2228K| 17M (0)| | 10 | TABLE ACCESS FULL | T1 | 100K| 10M| | 448 (1)| 00:00:06 | | | | | 11 | TABLE ACCESS FULL | T2 | 100K| 10M| | 448 (1)| 00:00:06 | | | | ------------------------------------------------------------------------------------------------------------------------------ --//你可以发现v_t34 查询到的结果无法推入v_t12x视图(使用group by的情况)。 SCOTT@book> select /*+ no_merge(v_t12) */ vc_t2 from v_t12y v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42; VC_T2 ---------------------------------------------------------------------------------------------------- t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7wywav830vvcn, child number 0 ------------------------------------- select /*+ no_merge(v_t12) */ vc_t2 from v_t12y v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42 Plan hash value: 779566306 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 8 (100)| | | | | | 1 | NESTED LOOPS | | 1 | 69 | 8 (13)| 00:00:01 | | | | | 2 | MERGE JOIN CARTESIAN | | 1 | 15 | 3 (0)| 00:00:01 | | | | | 3 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 10 | 2 (0)| 00:00:01 | | | | |* 4 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | 1 (0)| 00:00:01 | | | | | 5 | BUFFER SORT | | 1 | 5 | 1 (0)| 00:00:01 | 2048 | 2048 | 2048 (0)| |* 6 | INDEX RANGE SCAN | I_T4_ID1 | 1 | 5 | 1 (0)| 00:00:01 | | | | | 7 | VIEW PUSHED PREDICATE | V_T12Y | 1 | 54 | 5 (20)| 00:00:01 | | | | | 8 | SORT UNIQUE | | 1 | 222 | 5 (20)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 9 | NESTED LOOPS | | 1 | 222 | 4 (0)| 00:00:01 | | | | | 10 | NESTED LOOPS | | 1 | 222 | 4 (0)| 00:00:01 | | | | | 11 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 111 | 2 (0)| 00:00:01 | | | | |* 12 | INDEX RANGE SCAN | I_T1_ID2 | 1 | | 1 (0)| 00:00:01 | | | | |* 13 | INDEX RANGE SCAN | I_T2_ID1 | 1 | | 1 (0)| 00:00:01 | | | | | 14 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 111 | 2 (0)| 00:00:01 | | | | ------------------------------------------------------------------------------------------------------------------------- --//而使用v_t12y视图(使用distinct的情况)，可以使用VIEW PUSHED PREDICATE。 --//而如果加入提示push_pred(v_t12)，无效。 SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42; VC_T2 ---------------------------------------------------------------------------------------------------- t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 78z3sp7dfavub, child number 2 ------------------------------------- select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,v_t34 where v_t12.id2_t1=v_t34.id2_t3 and v_t34.id1_t3=42 Plan hash value: 3080093223 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 6883 (100)| | | | | |* 1 | HASH JOIN | | 1 | 80 | | 6883 (1)| 00:01:23 | 2440K| 2440K| 657K (0)| | 2 | MERGE JOIN CARTESIAN | | 1 | 15 | | 3 (0)| 00:00:01 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 10 | | 2 (0)| 00:00:01 | | | | |* 4 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | | 1 (0)| 00:00:01 | | | | | 5 | BUFFER SORT | | 1 | 5 | | 1 (0)| 00:00:01 | 2048 | 2048 | 2048 (0)| |* 6 | INDEX RANGE SCAN | I_T4_ID1 | 1 | 5 | | 1 (0)| 00:00:01 | | | | | 7 | VIEW | V_T12X | 100K| 6347K| | 6880 (1)| 00:01:23 | | | | | 8 | HASH GROUP BY | | 100K| 21M| 22M| 6880 (1)| 00:01:23 | 26M| 4058K| 25M (0)| |* 9 | HASH JOIN | | 100K| 21M| 11M| 2061 (1)| 00:00:25 | 15M| 2228K| 17M (0)| | 10 | TABLE ACCESS FULL | T1 | 100K| 10M| | 448 (1)| 00:00:06 | | | | | 11 | TABLE ACCESS FULL | T2 | 100K| 10M| | 448 (1)| 00:00:06 | | | | ------------------------------------------------------------------------------------------------------------------------------ --//视乎视图使用group by后无法推入。 3.继续测试： --//使用单表测试看看。 SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,t3 where v_t12.id2_t1=t3.id2 and t3.id1=42; VC_T2 ---------------------------------------------------------------------------------------------------- t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 8cd1sh12aggma, child number 1 ------------------------------------- select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12x v_t12,t3 where v_t12.id2_t1=t3.id2 and t3.id1=42 Plan hash value: 581210371 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 2066 (100)| | 1 |00:00:00.21 | 3233 | | | | |* 1 | HASH JOIN | | 1 | 1 | 75 | | 2066 (1)| 00:00:25 | 1 |00:00:00.21 | 3233 | 2440K| 2440K| 260K (0)| | 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 10 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 10 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | |* 4 | INDEX RANGE SCAN | I_T3_ID1 | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | | 5 | VIEW | V_T12X | 1 | 3 | 195 | | 2064 (1)| 00:00:25 | 3 |00:00:00.21 | 3230 | | | | | 6 | HASH GROUP BY | | 1 | 3 | 666 | | 2064 (1)| 00:00:25 | 3 |00:00:00.21 | 3230 | 761K| 761K| 725K (0)| | 7 | JOIN FILTER USE | :BF0000 | 1 | 100K| 21M| | 2061 (1)| 00:00:25 | 3 |00:00:00.21 | 3230 | | | | |* 8 | HASH JOIN | | 1 | 100K| 21M| 11M| 2061 (1)| 00:00:25 | 100K|00:00:00.20 | 3230 | 15M| 2228K| 17M (0)| | 9 | TABLE ACCESS FULL | T1 | 1 | 100K| 10M| | 448 (1)| 00:00:06 | 100K|00:00:00.02 | 1615 | | | | | 10 | TABLE ACCESS FULL | T2 | 1 | 100K| 10M| | 448 (1)| 00:00:06 | 100K|00:00:00.02 | 1615 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T3@SEL$1 4 - SEL$1 / T3@SEL$1 5 - SEL$335DD26A / V_T12@SEL$1 6 - SEL$335DD26A 9 - SEL$335DD26A / T1@SEL$3 10 - SEL$335DD26A / T2@SEL$3 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("V_T12"."ID2_T1"="T3"."ID2") 4 - access("T3"."ID1"=42) 8 - access("T1"."ID1"="T2"."ID1") Note ----- - cardinality feedback used for this statement --//你可以发现一样无法推入视图v_t12,在使用group by的情况下。 --//另外说明一点，以前我一直以为布隆过滤仅仅出现在exadata的机器上，实际上普通服务器也支持，只不过很少能看到。 --//可以设置 alter session set "_bloom_filter_enabled"=false ;再测试就看不到上面的 JOIN FILTER USE。而且布隆过滤在这里确 --//实快一点点。 SCOTT@book> select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12y v_t12,t3 where v_t12.id2_t1=t3.id2 and t3.id1=42; VC_T2 ---------------------------------------------------------------------------------------------------- t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2t2 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID a00sa3x25d2q9, child number 0 ------------------------------------- select /*+ no_merge(v_t12) push_pred(v_t12) */ vc_t2 from v_t12y v_t12,t3 where v_t12.id2_t1=t3.id2 and t3.id1=42 Plan hash value: 1850358640 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 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 | | | 7 (100)| | 1 |00:00:00.01 | 10 | | | | | 1 | NESTED LOOPS | | 1 | 1 | 64 | 7 (15)| 00:00:01 | 1 |00:00:00.01 | 10 | | | | | 2 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 1 | 10 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | | |* 3 | INDEX RANGE SCAN | I_T3_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | | 4 | VIEW PUSHED PREDICATE | V_T12Y | 1 | 1 | 54 | 5 (20)| 00:00:01 | 1 |00:00:00.01 | 6 | | | | | 5 | SORT UNIQUE | | 1 | 1 | 222 | 5 (20)| 00:00:01 | 1 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)| | 6 | NESTED LOOPS | | 1 | 1 | 222 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | | | 7 | NESTED LOOPS | | 1 | 1 | 222 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 111 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | |* 9 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | |* 10 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | | 11 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 111 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T3@SEL$1 3 - SEL$1 / T3@SEL$1 4 - SEL$639F1A6F / V_T12@SEL$1 5 - SEL$639F1A6F 8 - SEL$639F1A6F / T1@SEL$2 9 - SEL$639F1A6F / T1@SEL$2 10 - SEL$639F1A6F / T2@SEL$2 11 - SEL$639F1A6F / T2@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T3"."ID1"=42) 9 - access("T1"."ID2"="T3"."ID2") 10 - access("T1"."ID1"="T2"."ID1") --//而使用distinct的视图v_t12y就可以推入。 4.当然如果不使用no_merge,可以通过查询变换获得合理的执行计划。我不再贴出执行计划。大家可以自行测试。 --//我们生产系统语句实在太复杂了，本来想通过提示找到合理执行计划，想想有点浪费时间，先修改视图定义采用distinct再说。
[20200117]push_pred distinct group by.txt
来源：这里教程网
时间：2026-03-03 14:47:36
作者：
编辑推荐：
- [20200117]push_pred distinct group by.txt03-03
- 体系_表空间和数据文件的管理03-03
- Oracle 扩充磁盘空间03-03
- Oracle不同版本查看数据库session/system级别设置了哪些events03-03
- OracleASM关闭AMM，打开ASMM，修改参数后报ORA-00843 ORA-0084903-03
- Oracle修改instance_name、db_name、db_unique_name、service_names03-03
- [20200111]浅谈exadata oltp系统的优化.txt03-03
- 动态参数与静态参数的判断、修改03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 体系_表空间和数据文件的管理
体系_表空间和数据文件的管理
26-03-03 - Oracle 扩充磁盘空间
Oracle 扩充磁盘空间
26-03-03 - Oracle修改instance_name、db_name、db_unique_name、service_names
- 数据库性能需求分析及评估模型
数据库性能需求分析及评估模型
26-03-03 - 了解这一点轻松解决Oracle数据库系统报错问题
了解这一点轻松解决Oracle数据库系统报错问题
26-03-03 - AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
- rman备份
rman备份
26-03-03 - Oracle增加控制文件副本
Oracle增加控制文件副本
26-03-03 - oracle分区表详解
oracle分区表详解
26-03-03 - Oracle 12c新特性维护表分区Global Index不失效
Oracle 12c新特性维护表分区Global Index不失效
26-03-03
