[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再说。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2673945/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
