33.读书笔记收获不止Oracle之表连接的排序分析

三种连接方式，哪种会用到排序？

哈希连接并不排序，消耗内存是用于建立HASH表。

嵌套循环不需要排序。

排序合并需要排序。

关于哈希连接、排序合并连接，有一个很简单的优化思想：不要取多余的字段参与排序。

1. 排序只取部分字段

1.1实验1

SQL>alter session set statistics_level=all;

SQL>select /*+leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_idand t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID dz2rnd44fg2jb,child number 0

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

select /*+leading(t2) use_merge(t1)*/ *from t1,t2 where t1.id=t2.t1_id

and t1.n=19

Plan hash value: 1792967693

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

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

| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buf

PLAN_TABLE_OUTPUT

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

fers | OMem| 1Mem | Used-Mem |

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

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

| 0| SELECT STATEMENT | | 1 | | 1 |00:00:00.07 |

1012 | | | |

| 1| MERGE JOIN | | 1 | 1 | 1|00:00:00.07 |

1012 | | | |

PLAN_TABLE_OUTPUT

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

| 2| SORT JOIN | | 1 | 100K| 20|00:00:00.07 |

1005 | 9762K| 1209K| 8677K (0)|

| 3| TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 |

1005 | | | |

|* 4| SORT JOIN | | 20 | 1 | 1 |00:00:00.01 |

7| 2048 | 2048 | 2048 (0)|

|* 5| TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 |

7 | | | |

PLAN_TABLE_OUTPUT

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

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

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

Predicate Information (identified byoperation id):

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

4- access("T1"."ID"="T2"."T1_ID")

filter("T1"."ID"="T2"."T1_ID")

5- filter("T1"."N"=19)

PLAN_TABLE_OUTPUT

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

25 rows selected.

1.2实验2

SQL>select /*+leading(t2) use_merge(t1)*/ t1.id from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID 8m84q4r08jgrt,child number 0

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

select /*+leading(t2) use_merge(t1)*/ t1.idfrom t1,t2 where

t1.id=t2.t1_id and t1.n=19

Plan hash value: 1792967693

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

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

| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buf

PLAN_TABLE_OUTPUT

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

fers | OMem| 1Mem | Used-Mem |

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

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

| 0| SELECT STATEMENT | | 1 | | 1 |00:00:00.08 |

1012 | | | |

| 1| MERGE JOIN | | 1 | 1 | 1 |00:00:00.08 |

1012 | | | |

PLAN_TABLE_OUTPUT

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

| 2| SORT JOIN | | 1 | 100K| 20|00:00:00.08 |

1005 | 2462K| 719K| 2188K (0)|

| 3| TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 |

1005 | | | |

|* 4| SORT JOIN | | 20 | 1 | 1 |00:00:00.01 |

7| 2048 | 2048 | 2048 (0)|

|* 5| TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 |

7| | | |

PLAN_TABLE_OUTPUT

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

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

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

Predicate Information (identified byoperation id):

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

4- access("T1"."ID"="T2"."T1_ID")

filter("T1"."ID"="T2"."T1_ID")

5- filter("T1"."N"=19)

PLAN_TABLE_OUTPUT

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

25 rows selected.

小结：

部分字段排序只用了2188K+2048，全部字段排序使用了8677K+2048.使用部分字段排序使用的资源是要少的。

如果表的字段更多一些，PGA空间不够容纳排序区，导致排序在磁盘中进行，性能将会出现数量级下降。很多开发同学用惯了SELECT *，这个一定要注意。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!