[20200117]push_pred distinct group by.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2673945/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL DBA(169) - Develop(Distinct vs Group by)SQLdev
- SQL -去重Group by 和Distinct的效率SQL
- oracle中distinct和group by的區別Oracle
- MySQL 中的 distinct 和 group by 的效能比較MySql
- oracle之優化一用group by或exists優化distinctOracle優化
- [20200117]ashtop指令碼使用簡介.txt指令碼
- Saprk distinct
- [20210627]cursor_sharing=force與orade by.txt
- Subarray Distinct Values
- uniq(uid) distinct uidUI
- RxJava_distinct&distinctUntilChangedRxJava
- 7.14 APPROX_COUNT_DISTINCTAPP
- [LeetCode] 115. Distinct SubsequencesLeetCode
- Oracle vs PostgreSQL Develop（15） - DISTINCT ONOracleSQLdev
- 7.15 APPROX_COUNT_DISTINCT_AGGAPP
- 7.16 APPROX_COUNT_DISTINCT_DETAILAPPAI
- 【Leetcode】1081. Smallest Subsequence of Distinct CharactersLeetCode
- sql - distinct 去重複的用法SQL
- [20201116]11g連線謂詞推入push_pred問題.txt
- group conv
- 【PostgreSQL 】PostgreSQL 15對distinct的優化SQL優化
- distinct 去重需要注意的地方
- 資料庫的sort group by和hash group by資料庫
- group_replication_bootstrap_group 用於什麼boot
- oracle partition by group by,詳解partition by和group by對比Oracle
- Solution - Atcoder Atcoder ARC137C Distinct Numbers
- MySQL distinct 和 order by 排序混淆的替代方案MySql排序
- 30 天精通 RxJS （15）：Observable Operators - distinct， distinctUntilChangedJS
- World Tour Finals 2019 D - Distinct Boxes 題解
- C#黔驢技巧之去重（Distinct）C#
- 【Leetcode】1180. Count Substrings with Only One Distinct LetterLeetCode
- 對含distinct操作的SQL的優化SQL優化
- MySQL Group ReplicationMySql
- Group by 優化優化
- [LeetCode] Group AnagramLeetCode
- 大資料下的Distinct Count（二）：Bitmap篇大資料
- Group by 最佳化
- 7.98 GROUP_ID