前言

最近接到個查詢任務，需要根據交易類型表（type_order）關聯交易表（main_order）進行統計202407-202506時間段的筆數。開發提供的SQL在備庫查詢了2個小時才有的結果，通過在主庫查看執行計劃兩張表全部為全表掃描，為了不影響主庫負載，于是把表導到壓測庫看看加個索引是不是能提供效率。但結果出乎意料…

示例操作：

• 表數據介紹：

SYS@twodb:1634> select count(*) from type_order;
  COUNT(*)
----------
        95

SYS@twodb:1634> select count(*) from main_order ;
  COUNT(*)
----------
 188936311

• 原SQL及執行計劃

SYS@twodb:1634> select count(*) from  main_order t
  2    join type_order d    on t.txn_code = d.txn_code   and t.txn_channel = d.txn_channel
  3   where t.trans_time >= TO_TIMESTAMP('20240701', 'yyyymmdd')
  4     and t.trans_time < TO_TIMESTAMP('20250701', 'yyyymmdd')
  5     and t.txn_status = 1000
  6     and d.prod_id in ('p1','p2','p3','p4','p5','p6','p7', 'p8','p9');
Elapsed: 00:02:07.13
Execution Plan
----------------------------------------------------------
Plan hash value: 1221874830
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |    43 |  1320K  (2)| 05:08:10 |       |       |
|   1 |  SORT AGGREGATE            |              |     1 |    43 |            |          |       |       |
|*  2 |   HASH JOIN                |              |  4499K|   184M|  1320K  (2)| 05:08:10 |       |       |
|*  3 |    TABLE ACCESS FULL       | type_order   |     9 |   171 |     3   (0)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ITERATOR|              |   145M|  3341M|  1319K  (2)| 05:07:55 |   106 |   117 |
|*  5 |     TABLE ACCESS FULL      | main_order   |   145M|  3341M|  1319K  (2)| 05:07:55 |   106 |   117 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."txn_code"="D"."txn_code" AND "T"."txn_channel"="D"."txn_channel")
   3 - filter("D"."prod_id"='p3' OR "D"."prod_id"='p4' OR
              "D"."prod_id"='p6' OR "D"."prod_id"='p7' OR "D"."prod_id"='p2' OR
              "D"."prod_id"='p5' OR "D"."prod_id"='p1' OR "D"."prod_id"='p8' OR
              "D"."prod_id"='p9')
   5 - filter("T"."txn_status"=1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    3933934  consistent gets
    3933857  physical reads
          0  redo size
        530  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

• 創建索引

SYS@twodb:1634> create index idx_order_1 on main_order (txn_code, txn_channel) nologging  local;  
Index created

• 創建索引后的執行計劃

SYS@twodb:1634> /
Elapsed: 00:10:45.61
Execution Plan
----------------------------------------------------------
Plan hash value: 2168199550
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    43 | 74806   (1)| 00:17:28 |       |       |
|   1 |  SORT AGGREGATE                     |             |     1 |    43 |            |          |       |       |
|   2 |   NESTED LOOPS                      |             |  4499K|   184M| 74806   (1)| 00:17:28 |       |       |
|   3 |    NESTED LOOPS                     |             |  4499K|   184M| 74806   (1)| 00:17:28 |       |       |
|*  4 |     TABLE ACCESS FULL               | type_order  |     9 |   171 |     3   (0)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE ITERATOR        |             |   266K|       |   676   (1)| 00:00:10 |   106 |   117 |
|*  6 |      INDEX RANGE SCAN               | IDX_ORDER_1 |   266K|       |   676   (1)| 00:00:10 |   106 |   117 |
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID| main_order  |   499K|    11M| 21328   (1)| 00:04:59 |     1 |     1 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("D"."prod_id"='p3' OR "D"."prod_id"='p4' OR "D"."prod_id"='p6' OR
              "D"."prod_id"='p7' OR "D"."prod_id"='p2' OR "D"."prod_id"='p5' OR
              "D"."prod_id"='p1' OR "D"."prod_id"='p8' OR "D"."prod_id"='p9')
   6 - access("T"."txn_code"="D"."txn_code" AND "T"."txn_channel"="D"."txn_channel")
   7 - filter("T"."txn_status"=1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   10146784  consistent gets
    4662946  physical reads
       8116  redo size
        530  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

前后對比

• 全表掃描要比走索引效率高80%
  

HASH JOIN

• HASH連接的算法：
  1、Build 階段：讀取小表（Build Input）生成Hash表；
  2、Probe 階段：讀取大表（Probe Input）探查Hash表并進行連接；
  3、Build 操作在PGA中進行，不夠時使用臨時表空間；
  4、被驅動表（被驅動表不需要讀入PGA中），對被驅動表的連接列也進行hash運算，然后到PGA中去探測Hash表進行連接；

• HINT用法：

SELECT /*+leading(t1) use_hash(t2)*/ * 
FROM T1 
INNER JOIN ON T1.ID = T2.ID;

• 注意
  1、當表數據太大PGA放不下，會使用臨時表空間，從而影響性能。示例：Oracle HASH JOIN 引起的TEMP爆滿分析總結；
  2、驅動順序：小的結果集先訪問，大的結果集后訪問，才能保證被驅動表的訪問次數降到最低，才能保障性能；
  3、驅動表和被驅動表都只會訪問0次（索引能返回數據，就不需要再回表）或者1次；
  4、HASH連接的驅動表與被驅動表的連接列都不需要創建索引；
  5、HASH連接主要用于處理兩表等值關聯；
  6、海量數據連接非?？欤?/li>

NESTED LOOPS

• 嵌套循環的算法：
  有驅動順序，驅動表返回多少條記錄，被驅動表就訪問多少次，嵌套循環連接中無須排序。
• HINT用法：t1為驅動表，t2為被驅動表

select /*+leading(t1) use_nl(t2)*/ * 
from t1 
join t2 on t1.id=t2.id;

• 注意：
  1、非常依賴索引，被驅動表索引只能：INDEX UNIQUE SCAN、INDEX RANGE SCAN。不能：TABLE ACCESS FULL、INDEX FULL SCAN、INDEX SKIP SCAN、INDEX FAST FULL SCAN，否則消耗太高，容易跑不出數據。
  2、DBLINK永遠不能作為NL的被驅動表。
  3、小結果集做驅動表，大結果集做被驅動表，才能保障性能。
  4、兩表使用外連接進行關聯，如果是NESTED LOOPS，那么無法更改驅動表，驅動表將會被固定在主表。
  5、連接條件是instr、LIKE、substr、regexp_like只能走NESTED LOOPS

小結：

• 兩表關聯返回少量數據應該走嵌套循環，兩表關聯返回大量數據應該走HASH連接。
• 此示例：type_order表1條數據對應main_order表多條數據，關聯返回的結果集1.34億條數據，因此HASH連接相對效率要高。
• HASH看體積，NL看行數。HASH看體積，因為HASH是要全部放內存的。HASH因為單個進程最大2G，所以要看體積。
• HASH連接驅動表非常大優化方案：開并行，并行之后就不是一個進程在HASH。

查詢為什么產生redo？

在Oracle數據庫中，盡管SELECT語句通常是只讀操作，理論上不應對數據產生修改，但在特定場景下仍可能觸發redo日志的生成。這些情況主要源于Oracle內部機制對數據一致性、事務管理和存儲結構的維護需求。以下是SELECT產生redo的主要原因：

• 1、延遲塊清除（Delayed Block Cleanout）?
  當事務提交后，如果修改的數據塊已被刷出內存（如因DBWR進程寫入磁盤），后續SELECT讀取這些塊時，Oracle需要清除塊上的事務信息（如ITL條目中的鎖標志和行頭鎖定位），此清理操作會生成redo日志?。這在處理大事務修改的塊時尤為常見。
• 2、維護讀一致性（Read Consistency）?
  為實現查詢開始時刻的數據一致性視圖，SELECT可能訪問undo數據塊來重建舊版本數據。如果undo數據塊首次被加載到內存，此過程可能記錄undo表空間的變化產生redo?。
• 3、直接路徑讀取（Direct Path Reads）?
  在處理大批量數據掃描（如全表掃描或排序操作）時，Oracle可能繞過buffer cache采用直接路徑讀取。若此過程需要分配臨時段（例如用于排序或哈希連接），臨時段的分配動作會生成redo日志?。
• 4、遞歸SQL操作（Recursive SQL）?
  Oracle內部自動執行的維護操作（如數據字典統計信息更新或索引重建），可能在SELECT訪問對象時觸發后臺修改系統表，間接產生redo日志?。
• 5、Lost Write Detection機制?
  為防止數據寫入丟失，Oracle在特定場景（如啟用自動檢測功能）下，SELECT讀取塊時會校驗塊完整性，若檢測到潛在丟失寫入風險，則生成redo日志以記錄校驗信息，導致大量redo產生?。
• 6、塊清理操作（Block Cleanout）?
  對于延遲清理狀態的“臟塊”（如先前DML操作未完全提交的塊），SELECT讀取時需要完成清理事務標記（如設置commit SCN），該操作會修改塊頭部并生成redo?。

盡管這些情況在OLTP系統中相對少見，但高并發事務或大負載查詢仍可能顯著增加redo日志量。優化建議包括減少大事務提交頻率、避免不必要的全表掃描，或調整參數如_db_block_checking以減輕檢測機制的開銷?。

為什么INDEX RANGE SCAN + NESTED LOOPS 會產生REDO？未找到對應的情況，后續如果有思路再補充。

• ash報告截圖：沒有“Direct Path Reads”事件
  

總結&疑問

• 1、索引優化不是萬能的；
• 2、沒想明白為什么在備庫查需要2個小時，導出來放壓測庫只需要2分種？
• 3、此次的查詢為什么會產生redo，沒有搞明白，和總結的特征都不對癥！

希望大家有思路的話，可以留言溝通，成長于在不斷的學習。

引用

2019云和恩墨大講堂：8.1 Join原理與優化
Oracle執行select會產生redo嗎？
Oracle表連接優化思路-嵌套查詢/哈希連接/排序合并連接等

測試文檔下載

Oracle 表連接NESTED LOOPS、HASH JOIN、排序合并.pdf