Oracle 執行計畫中的 BUFFER SORT 並非真正排序

Oracle 執行計畫中的 BUFFER SORT 並非真正排序

在執行計畫中看到 BUFFER SORT，許多人誤以為發生排序。實測證明：BUFFER SORT 僅將資料載入記憶體以避免重複掃描表，不進行實際排序。

實驗驗證

-- 建立測試資料
DROP TABLE test PURGE;
DROP TABLE test1 PURGE;
CREATE TABLE test AS SELECT * FROM dba_objects WHERE rownum < 10;
CREATE TABLE test1 AS SELECT * FROM dba_objects WHERE rownum < 10;
CREATE INDEX ind_t_object_id ON test(object_id);
CREATE INDEX ind_t1_object_id ON test1(object_id);
EXEC dbms_stats.gather_table_stats(user,'test',cascade=>true);
EXEC dbms_stats.gather_table_stats(user,'test1',cascade=>true);

-- 執行查詢（故意誘發 Cartesian Merge Join）
SELECT /*+gg_test*/ * FROM test t, test1 t1 WHERE t.object_name LIKE 'T%';

執行計畫片段

| Id | Operation            | Name  | Rows | Bytes | Cost | Time     |
|  1 | MERGE JOIN CARTESIAN |       |    9 |  1350 |    6 | 00:00:01 |
|  2 | TABLE ACCESS FULL    | TEST  |    1 |    75 |    3 | 00:00:01 |
|  3 | BUFFER SORT          |       |    9 |   675 |    3 | 00:00:01 |
|  4 | TABLE ACCESS FULL    | TEST1 |    9 |   675 |    3 | 00:00:01 |

關鍵證據：#keys=0 表示無排序欄位

SELECT projection FROM v$sql_plan_statistics_all
WHERE sql_id IN (SELECT sql_id FROM v$sql WHERE sql_text LIKE 'select /*+gg_test*/* from test t, test1 t1 where t.object_name like ''T%''')
  AND operation='BUFFER' AND options='SORT';

-- 回傳結果
(#keys=0) "T1"."OWNER"[VARCHAR2,30], "T1"."OBJECT_NAME"[VARCHAR2,128], ...

#keys=0 明確指出此操作未對任何欄位排序，僅作緩衝用途。

結論

• BUFFER SORT ≠ 排序，不消耗暫存表空間做排序運算。
• 判斷是否真排序，查 v$sql_plan_statistics_all.projection 的 #keys 值：0 為無排序，>0 才是真排序。