背景
此SQL運營反饋執行了一上午都沒出結果，讓我看看有沒有優化方案，因為他們每月都需要執行一次統計數據。為了不影響生產環境，對相關表（expdp/impdp）導入到測試環境后15分鐘就出結果了（可能是統計信息過舊或生產庫中表碎片的原因，在此不做深入探究）。
- 初始SQL
select count(distinct c.cust_id)
from main_order t
join order_port a on t.port_id = a.port_id
join cust_info c on a.cust_id = c.cust_id
where t.tran_time >= to_date('2025-04-01', 'yyyy-mm-dd')
and t.tran_time < to_date('2025-07-01', 'yyyy-mm-dd')
and c.cust_id <> 'C00001';
返回結果：533
- 數據量
| 表名稱 | 數據量 | 備注 |
|---|---|---|
| MAIN_ORDER | 367357262 | 分區字段：tran_time |
| ORDER_PORT | 799286 | - |
| CUST_INFO | 12133 | - |
- 索引
| 表名稱 | 類型 | 索引名稱 | 字段 |
|---|---|---|---|
| CUST_INFO | 主鍵 | PK_CUST_INFO | CUST_ID |
| ORDER_PORT | 主鍵 | PK_ORDER_PORT | PORT_ID |
| MAIN_ORDER | 普通 | AATD_IDX2 | PORT_ID |
開始改寫
先理解SQL邏輯：匯總時間范圍：202504-202506 三個月中，剔除測試商戶號：C00001，有交易的商戶數。
失敗的改寫
- 第一優化點：distinct 去重效率低（網上經驗表示），建議使用group by 去重；此觀點不絕對！
- 第二優化點：剔除測試商戶號：C00001，可以匯總完后-1來實現，省略“<>”操作；此觀點錯誤！
- 改寫后SQL:
select count(*)-1
from (select t.port_id
from main_order t
where t.tran_time >= to_date('2025-04-01', 'yyyy-mm-dd')
and t.tran_time < to_date('2025-07-01', 'yyyy-mm-dd')
group by t.port_id) b
join order_port a on b.port_id = a.port_id
join cust_info c on a.cust_id = c.cust_id;
記錄數：543（失敗）
返回結果543與初始SQL的結果533多出了10條記錄。用了一上午梳理邏輯都感覺沒問題，最后查了下cust_info 表里的cust_id字段的數據，發現不是唯一值：C00001正好有11條重復值。改寫不能只考慮SQL邏輯，還應該結合表里的數據。此改寫SQL方案失敗。
低效率的改寫
- 總結問題二次改寫SQL
select count(*)-1
from (select c.cust_id
from main_order t
join order_port a on t.port_id = a.port_id
join cust_info c on a.cust_id = c.cust_id
where t.tran_time >= to_date('2025-04-01', 'yyyy-mm-dd')
and t.tran_time < to_date('2025-07-01', 'yyyy-mm-dd')
group by c.cust_id);
記錄數：533
- 刷新數據庫內存
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
- 執行計劃
Elapsed: 00:19:20.78
Execution Plan
----------------------------------------------------------
Plan hash value: 81192362
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1361K (1)| 05:17:35 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | VM_NWVW_0 | 12015 | | | 1361K (1)| 05:17:35 | | |
| 3 | SORT GROUP BY NOSORT | | 12015 | 880K| | 1361K (1)| 05:17:35 | | |
| 4 | NESTED LOOPS | | 2407K| 172M| | 1361K (1)| 05:17:35 | | |
| 5 | NESTED LOOPS | | 25M| 172M| | 1361K (1)| 05:17:35 | | |
| 6 | MERGE JOIN | | 12108 | 520K| | 11760 (1)| 00:02:45 | | |
| 7 | SORT JOIN | | 12015 | 140K| | 31 (7)| 00:00:01 | | |
| 8 | INDEX FAST FULL SCAN | PK_CUST_INFO| 12015 | 140K| | 29 (0)| 00:00:01 | | |
|* 9 | SORT JOIN | | 798K| 24M| 61M| 11729 (1)| 00:02:45 | | |
| 10 | TABLE ACCESS FULL | ORDER_PORT | 798K| 24M| | 7330 (1)| 00:01:43 | | |
| 11 | PARTITION RANGE ITERATOR | | 2100 | | | 31 (0)| 00:00:01 | 115 | 117 |
|* 12 | INDEX RANGE SCAN | AATD_IDX2 | 2100 | | | 31 (0)| 00:00:01 | 115 | 117 |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID| MAIN_ORDER | 199 | 6169 | | 651 (1)| 00:00:10 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("A"."CUST_ID"="C"."CUST_ID")
filter("A"."CUST_ID"="C"."CUST_ID")
12 - access("T"."port_id"="A"."port_id")
Statistics
----------------------------------------------------------
3010 recursive calls
0 db block gets
33845627 consistent gets
3750470 physical reads
0 redo size
529 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
276 sorts (memory)
0 sorts (disk)
1 rows processed
- 初始SQL執行計劃
Elapsed: 00:15:47.82
Execution Plan
----------------------------------------------------------
Plan hash value: 1601498716
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | | 1360K (1)| 05:17:34 | | |
| 1 | SORT AGGREGATE | | 1 | 7 | | | | | |
| 2 | VIEW | VW_DAG_0 | 12014 | 84098 | | 1360K (1)| 05:17:34 | | |
| 3 | SORT GROUP BY NOSORT | | 12014 | 879K| | 1360K (1)| 05:17:34 | | |
| 4 | NESTED LOOPS | | 2407K| 172M| | 1360K (1)| 05:17:34 | | |
| 5 | NESTED LOOPS | | 25M| 172M| | 1360K (1)| 05:17:34 | | |
| 6 | MERGE JOIN | | 12107 | 520K| | 11760 (1)| 00:02:45 | | |
| 7 | SORT JOIN | | 12014 | 140K| | 31 (7)| 00:00:01 | | |
|* 8 | INDEX FAST FULL SCAN | PK_CUST_INFO | 12014 | 140K| | 29 (0)| 00:00:01 | | |
|* 9 | SORT JOIN | | 798K| 24M| 61M| 11729 (1)| 00:02:45 | | |
|* 10 | TABLE ACCESS FULL | ORDER_PORT | 798K| 24M| | 7330 (1)| 00:01:43 | | |
| 11 | PARTITION RANGE ITERATOR | | 2100 | | | 31 (0)| 00:00:01 | 115 | 117 |
|* 12 | INDEX RANGE SCAN | AATD_IDX2 | 2100 | | | 31 (0)| 00:00:01 | 115 | 117 |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID| MAIN_ORDER | 199 | 6169 | | 651 (1)| 00:00:10 | 1 | 1 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("C"."CUST_ID"<>'C00001')
9 - access("A"."CUST_ID"="C"."CUST_ID")
filter("A"."CUST_ID"="C"."CUST_ID")
10 - filter("A"."CUST_ID"<>'C00001')
12 - access("T"."port_id"="A"."port_id")
Statistics
----------------------------------------------------------
3047 recursive calls
0 db block gets
28411428 consistent gets
3056785 physical reads
0 redo size
543 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
269 sorts (memory)
0 sorts (disk)
1 rows processed
- 效率對比
結果一致都是533，但執行耗時及消耗沒有提升反而下降：18%，因此改寫失敗。
其它改寫失敗方案：
- 使用c.cust_id <> ‘C00001’ 條件比不使用提升2min,因此前面提到的用count(*)-1代替條件的思路錯誤。
- 執行計劃ORDER_PORT表一直是全表掃描，但CUST_ID字段有唯一索引，于是指定索引耗時： 00:14:55.26僅提升1min，仍然不理想。
select /*+ INDEX(a PK_CUST_ID) */
成功優化方案-TABLE ACCESS FULL
突發奇想：刪除索引全表掃描，觀察一下效率。沒想到劇情反轉：索引才是查詢效率的障礙！！！（最終驗證結果是索引導致表連接方式的改變，導致性能下降）
- 執行計劃
Elapsed: 00:01:46.39
Execution Plan
----------------------------------------------------------
Plan hash value: 44104297
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | | 1976K (1)| 07:41:07 | | |
| 1 | SORT AGGREGATE | | 1 | 7 | | | | | |
| 2 | VIEW | VW_DAG_0 | 12014 | 84098 | | 1976K (1)| 07:41:07 | | |
| 3 | HASH GROUP BY | | 12014 | 879K| 193M| 1976K (1)| 07:41:07 | | |
|* 4 | HASH JOIN | | 2407K| 172M| | 1963K (1)| 07:38:05 | | |
|* 5 | HASH JOIN | | 12107 | 520K| | 7753 (1)| 00:01:49 | | |
|* 6 | TABLE ACCESS FULL | CUST_INFO | 12014 | 140K| | 416 (1)| 00:00:06 | | |
|* 7 | TABLE ACCESS FULL | ORDER_PORT | 798K| 24M| | 7330 (1)| 00:01:43 | | |
| 8 | PARTITION RANGE ITERATOR| | 158M| 4696M| | 1954K (1)| 07:36:00 | 115 | 117 |
| 9 | TABLE ACCESS FULL | MAIN_ORDER | 158M| 4696M| | 1954K (1)| 07:36:00 | 115 | 117 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."port_id"="A"."port_id")
5 - access("A"."CUST_ID"="C"."CUST_ID")
6 - filter("C"."CUST_ID"<>'C00001')
7 - filter("A"."CUST_ID"<>'C00001')
Statistics
----------------------------------------------------------
1311 recursive calls
0 db block gets
3041240 consistent gets
3038903 physical reads
0 redo size
543 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
237 sorts (memory)
0 sorts (disk)
1 rows processed
- HIT 指定a,c,t全部全表掃描
SELECT /*+ FULL(a) FULL(c) FULL(t)*/
多次嘗試后發現，只有FULL(t)時，表連接方式為HASH JOIN，執行計劃如下：
Elapsed: 00:01:58.35
Execution Plan
----------------------------------------------------------
Plan hash value: 498325925
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | | 1975K (1)| 07:41:01 | | |
| 1 | SORT AGGREGATE | | 1 | 7 | | | | | |
| 2 | VIEW | VW_DAG_0 | 12014 | 84098 | | 1975K (1)| 07:41:01 | | |
| 3 | HASH GROUP BY | | 12014 | 879K| 193M| 1975K (1)| 07:41:01 | | |
|* 4 | HASH JOIN | | 2407K| 172M| | 1962K (1)| 07:38:00 | | |
|* 5 | HASH JOIN | | 12107 | 520K| | 7345 (1)| 00:01:43 | | |
|* 6 | INDEX FAST FULL SCAN | PK_CUST_INFO | 12014 | 140K| | 8 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | ORDER_PORT | 798K| 24M| | 7330 (1)| 00:01:43 | | |
| 8 | PARTITION RANGE ITERATOR| | 158M| 4696M| | 1954K (1)| 07:36:00 | 115 | 117 |
| 9 | TABLE ACCESS FULL | MAIN_ORDER | 158M| 4696M| | 1954K (1)| 07:36:00 | 115 | 117 |
---------------------------------------------------------------------------------------------------------------------
- 初始化SQL效率對比提升88%
這里就涉及到排序合并連接(SORT JOIN & MERGE JOIN）與HASH JOIN的知識點。 - HASH JOIN的知識點，關注文章：《性能優化-不合理的索引》有總結。
排序合并連接（SORT JOIN & MERGE JOIN）
- 連接算法：
1、Sort 階段：兩邊集合按照連接字段進行排序
2、Merge 階段：排序好的兩邊集合進行相互合并（Merge）操作。
3、兩張表只會訪問0次（索引能返回數據，就不需要再回表）或者1次；
4、Sort 、Merge 均在PGA中操作，PGA空間不夠時會使用臨時表空間。 - 支持排序合并連接的連接條件：支持>、>=和<、<=、<>之類的連接條件。
- 排序合并連接就是為了解決非等值關聯，并行返回數據量大的情況
- HINT用法：
SELECT /*+ordered use_merge(t2)*/ * FROM T1 INNER JOIN ON T1.ID = T2.ID
總結
- 1、去重操作：group by 比 distinct 效率高的觀點，不絕對視情況而定；
- 2、索引并不能解決所有的性能問題，有時可能會導致性能問題；切記盲目相信索引；
- 3、表的連接方式合適才是影響性能的關鍵，此示例：HASH JOIN比SORT&MERGE JOIN性能更好；
- 4、兩表等值關聯:返回數據量少：走NL連接更快，返回數據量多:大部分情況Hash連接比排序合并連接快；
引用
Oracle表連接優化思路-嵌套查詢/哈希連接/排序合并連接等
2019云和恩墨大講堂：8.1 Join原理與優化
測試文檔下載
Oracle 表連接NESTED LOOPS、HASH JOIN、排序合并.pdf
歡迎贊賞支持或留言指正
最后修改時間：2025-08-07 11:00:02
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
