【sql調優之執行計劃】merge join cartesian and buffer sort

資料庫版本：oracle 9208

有時在執行計劃中看到了笛卡爾連線，通常會發現是關聯有問題，例如：

SQL> select * from t_bank_class a,t_log_detail b;

no rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=4652)

1 0 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=4652)

2 1 TABLE ACCESS (FULL) OF 'T_LOG_DETAIL' (Cost=2 Card=1 Byt

es=4641)

3 1 BUFFER (SORT) (Cost=2 Card=6 Bytes=66)

4 3 TABLE ACCESS (FULL) OF 'T_BANK_CLASS' (Cost=2 Card=6 B

ytes=66)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

1 physical reads

0 redo size

795 bytes sent via SQL*Net to client

237 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL>

很多時候需要去檢查一下是否是sql邏輯有問題，但也有時並不是因為邏輯問題而導致資料庫選擇這樣的執行計劃，

例如：

SQL> select 1 from t_group_policy_product tgpp,

2 t_product_life tpl,

3 t_period_type tpy

4 where tgpp.policy_id = 24

5 and tgpp.product_id = tpl.product_id

6 and tgpp.main_rider = '1'

7 and tpl.period_type =tpy.period_type

8 and tpy.period_type in (1,2)

9 ;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=20)

1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=20)

2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=3 Bytes=42)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU

CT' (Cost=1 Card=2 Bytes=24)

4 3 INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN

IQUE) (Cost=3 Card=3)

5 2 BUFFER (SORT) (Cost=1 Card=2 Bytes=4)

6 5 INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE) (Co

st=1 Card=2 Bytes=4)

7 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cost=

1 Card=1 Bytes=6)

8 7 INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

206 bytes sent via SQL*Net to client

244 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

使用了MERGE JOIN (CARTESIAN)操作，笛卡爾合併連線，注意到這裡INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT'只會取到一行資料，

oracle僅僅將這個結果集與後面的 INDEX (FULL SCAN) OF 'PK_T_PERIOD_TYPE'的結果集放到一起（使用笛卡爾乘積），然後將結果與T_PRODUCT_LIFE連線（外層是個nested loop）,

這是因為做笛卡爾乘積的兩個表返回行的cardinality 比較小，故而會比較高效。

asktom上面也有個相似的例子：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4105951726381

將這個查詢語句稍作修改：

SQL> select * from t_group_policy_product tgpp,

2 t_product_life tpl,

3 t_period_type tpy

4 where tgpp.policy_id = 24

5 and tgpp.product_id = tpl.product_id

6 and tgpp.main_rider = '1'

7 and tpl.period_type =tpy.period_type

8 and tpy.period_type in (1,2)

9 ;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=1411)

1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=1411)

2 1 NESTED LOOPS (Cost=2 Card=2 Bytes=2798)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_GROUP_POLICY_PRODU

CT' (Cost=1 Card=2 Bytes=536)

4 3 INDEX (RANGE SCAN) OF 'UNI_GROUP_POLICY_PRODUCT' (UN

IQUE) (Cost=3 Card=3)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_PRODUCT_LIFE' (Cos

t=1 Card=1 Bytes=1131)

6 5 INDEX (UNIQUE SCAN) OF 'PK_T_PRODUCT_LIFE' (UNIQUE)

7 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_PERIOD_TYPE' (Cost=1

Card=1 Bytes=12)

8 7 INDEX (UNIQUE SCAN) OF 'PK_T_PERIOD_TYPE' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

11 consistent gets

1 physical reads

0 redo size

22202 bytes sent via SQL*Net to client

244 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

雖然cost和card都沒有改變，但由於使用了*使得需要從行中返回多個列，使得bytes值變得相對大很多（意味著可能使用更多的memory），

這時候可以看到執行計劃已經改變了，oracle使用nested loops代替了之前的merge join(CARTESIAN)。

而buffer (sort）則是指使用記憶體排序。一些操作（如Merge join 或者order by）需要對行集排序。Buffer sort使用的記憶體數量與資料量和 sort_area_size初始引數有關（如果workarea_size_policy引數被設定為AUTO，則與pga_aggregate_target引數相關），

為什麼這裡會使用buffer sort？一般來說，如果優化器傾向於使用IO cost model，謂詞中的過濾條件不止一列且沒有索引，而過濾條件預期的返回行只有一行，這種情況下可能會使用buffer sort，事實上，這個例子中執行計劃已經提示了：

Note: cpu costing is off, PLAN_TABLE' is old version

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2

| 1 | NESTED LOOPS | | 1 | 2

| 2 | MERGE JOIN CARTESIAN | | 3 | 4

| 3 | TABLE ACCESS BY INDEX ROWID| T_GROUP_POLICY_PRODUCT | 2 | 2

| 4 | INDEX RANGE SCAN | UNI_GROUP_POLICY_PRODUCT | 3 |

| 5 | BUFFER SORT | | 2 |

| 6 | INDEX FULL SCAN | PK_T_PERIOD_TYPE | 2 |

| 7 | TABLE ACCESS BY INDEX ROWID | T_PRODUCT_LIFE | 1 |

| 8 | INDEX UNIQUE SCAN | PK_T_PRODUCT_LIFE | 1 |

--------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

16 rows selected

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671083/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】merge join cartesian and buffer sort

相關文章