很多执行计划本身就是需要排序的，通过特定的执行计划可以避免排序的产生。前两天在论坛中看到类似的问题。这里简单总结一下。

利用执行计划来避免排序操作（一）：http://yangtingkun.itpub.net/post/468/242836

上面讨论了单表查询如何利用执行计划来避免排序，这篇文章简单一下包含连接的查询如何通过执行计划来避免排序。

MERGE JOIN连接方式由于是先排序后连接，因此查询的结果本是就是包含排序的。

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);

表已创建。

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T1_NAME ON T1(NAME);

索引已创建。

SQL> INSERT INTO T1 SELECT ROWNUM, TABLE_NAME FROM USER_TABLES;

已创建17行。

SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME FROM USER_OBJECTS;

已创建97行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON EXP
SQL> SELECT /*+ USE_MERGE(T1, T2) */ T2.NAME, T1.ID
2 FROM T1, T2
3 WHERE T1.NAME = T2.NAME
4 ;

NAME ID
------------------------------ ----------
BONUS 5
CHAINED_ROWS 2
DEPT 3
DR$IND_T_DOCS$I 1
DR$IND_T_DOCS$K 17
DR$IND_T_DOCS$N 16
DR$IND_T_DOCS$R 9
DUMMY 7
EMP 4
SALGRADE 6
T 8
T1 14
T2 15
T_BITMAP 13
T_DUMP 12
T_NOSORT 10
T_OTHER 11

已选择17行。

执行计划
----------------------------------------------------
Plan hash value: 412793182

----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 893 |
| 1 | MERGE JOIN | | 19 | 893 |
| 2 | SORT JOIN | | 17 | 510 |
| 3 | TABLE ACCESS FULL| T1 | 17 | 510 |
|* 4 | SORT JOIN | | 97 | 1649 |
| 5 | TABLE ACCESS FULL| T2 | 97 | 1649 |
----------------------------------------------------

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

4 - access("T1"."NAME"="T2"."NAME")
filter("T1"."NAME"="T2"."NAME")

因此，如果指定排序为连接列的升序排列，使用MERGE JOIN可以避免排序的产生。

SQL> SELECT /*+ USE_MERGE(T2, T1) */ T2.NAME, T1.ID
2 FROM T1, T2
3 WHERE T1.NAME = T2.NAME
4 ORDER BY T2.NAME
5 ;

NAME ID
------------------------------ ----------
BONUS 5
CHAINED_ROWS 2
DEPT 3
DR$IND_T_DOCS$I 1
DR$IND_T_DOCS$K 17
DR$IND_T_DOCS$N 16
DR$IND_T_DOCS$R 9
DUMMY 7
EMP 4
SALGRADE 6
T 8
T1 14
T2 15
T_BITMAP 13
T_DUMP 12
T_NOSORT 10
T_OTHER 11

已选择17行。

执行计划
----------------------------------------------------
Plan hash value: 412793182

----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 893 |
| 1 | MERGE JOIN | | 19 | 893 |
| 2 | SORT JOIN | | 17 | 510 |
| 3 | TABLE ACCESS FULL| T1 | 17 | 510 |
|* 4 | SORT JOIN | | 97 | 1649 |
| 5 | TABLE ACCESS FULL| T2 | 97 | 1649 |
----------------------------------------------------

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

4 - access("T1"."NAME"="T2"."NAME")
filter("T1"."NAME"="T2"."NAME")

但是MERGE JOIN只能对连接列排序，且排序操作只能是升序，对于降序MERGE JOIN都无能为力：

SQL> SELECT /*+ USE_MERGE(T2, T1) */ T2.NAME, T1.ID
2 FROM T1, T2
3 WHERE T1.NAME = T2.NAME
4 ORDER BY T2.NAME DESC
5 ;

NAME ID
------------------------------ ----------
T_OTHER 11
T_NOSORT 10
T_DUMP 12
T_BITMAP 13
T2 15
T1 14
T 8
SALGRADE 6
EMP 4
DUMMY 7
DR$IND_T_DOCS$R 9
DR$IND_T_DOCS$N 16
DR$IND_T_DOCS$K 17
DR$IND_T_DOCS$I 1
DEPT 3
CHAINED_ROWS 2
BONUS 5

已选择17行。

执行计划
-----------------------------------------------------
Plan hash value: 2216793107

-----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 893 |
| 1 | SORT ORDER BY | | 19 | 893 |
| 2 | MERGE JOIN | | 19 | 893 |
| 3 | SORT JOIN | | 17 | 510 |
| 4 | TABLE ACCESS FULL| T1 | 17 | 510 |
|* 5 | SORT JOIN | | 97 | 1649 |
| 6 | TABLE ACCESS FULL| T2 | 97 | 1649 |
-----------------------------------------------------

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

5 - access("T1"."NAME"="T2"."NAME")
filter("T1"."NAME"="T2"."NAME")

下面说一下NESTED LOOP连接，由于NESTED LOOP操作本身不包含排序，因此NESTED LOOP也不会保证结果是排序的。但是NESTED LOOP可以保证连接过程是稳定的，也就是说，如果驱动表在连接之前是排序的，那么经过连接之后，得到的结果也是排序的：

SQL> CREATE INDEX IND_T2_ID ON T2(ID);

索引已创建。

SQL> CREATE INDEX IND_T2_NAME ON T2(NAME);

索引已创建。

SQL> SELECT /*+ USE_NL(T1, T2) */ T1.ID, T1.NAME, T2.NAME
2 FROM T1, T2
3 WHERE T1.ID = T2.ID
4 ;

ID NAME NAME
---------- ------------------------------ ----------------------------
1 DR$IND_T_DOCS$I P_DEFINE_TYPE2
2 CHAINED_ROWS DR$IND_T_DOCS$I
3 DEPT P_TEST2
4 EMP CHAINED_ROWS
5 BONUS T_TEST1
6 SALGRADE T_TEST1
7 DUMMY P_TEST1
8 T DEPT
9 DR$IND_T_DOCS$R EMP
10 T_NOSORT BONUS
11 T_OTHER SALGRADE
12 T_DUMP DUMMY
13 T_BITMAP T
14 T1 IND_T_DOCS
15 T2 SYS_LOB0000071254C00006$$
16 DR$IND_T_DOCS$N DR$IND_T_DOCS$K
17 DR$IND_T_DOCS$K T_TEST3

已选择17行。

执行计划
----------------------------------------------------------
Plan hash value: 3621112097

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 1020 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 |
| 2 | NESTED LOOPS | | 17 | 1020 |
| 3 | TABLE ACCESS FULL | T1 | 17 | 510 |
|* 4 | INDEX RANGE SCAN | IND_T2_ID | 1 | |
----------------------------------------------------------------------

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

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

现在得到的结果不是按照T1的NAME列进行排序的，但是如果把T1作为驱动表，且访问T1的时候就按照NAME的顺序访问，那么得到的最终结果也是按照T1的NAME进行排序的：

SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME) USE_NL(T1, T2) */
2 T1.ID, T1.NAME, T2.NAME
3 FROM T1, T2
4 WHERE T1.ID = T2.ID
5 ORDER BY T1.NAME
6 ;

ID NAME NAME
---------- ------------------------------ ------------------------------
5 BONUS T_TEST1
2 CHAINED_ROWS DR$IND_T_DOCS$I
3 DEPT P_TEST2
1 DR$IND_T_DOCS$I P_DEFINE_TYPE2
17 DR$IND_T_DOCS$K T_TEST3
16 DR$IND_T_DOCS$N DR$IND_T_DOCS$K
9 DR$IND_T_DOCS$R EMP
7 DUMMY P_TEST1
4 EMP CHAINED_ROWS
6 SALGRADE T_TEST1
8 T DEPT
14 T1 IND_T_DOCS
15 T2 SYS_LOB0000071254C00006$$
13 T_BITMAP T
12 T_DUMP DUMMY
10 T_NOSORT BONUS
11 T_OTHER SALGRADE

已选择17行。

执行计划
----------------------------------------------------------
Plan hash value: 1062594094

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 1020 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 30 |
| 2 | NESTED LOOPS | | 17 | 1020 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
|* 5 | INDEX RANGE SCAN | IND_T2_ID | 1 | |
---------------------------------------------------------------------

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

5 - access("T1"."ID"="T2"."ID")

通过上面的方法，保证了进行连接之后，结果集的最终顺序。而且Oracle的优化器也足够的聪明，认识到了目前得到的结果就是根据NAME排序好的，因此忽略了SORT ORDER BY的操作。

根据NESTED LOOP的特点，甚至可以实现下面的功能：

SQL> ALTER TABLE T2 MODIFY NAME NOT NULL;

表已更改。

SQL> CREATE INDEX IND_T2_NAME ON T2(NAME);

索引已创建。

SQL> SET AUTOT OFF
SQL> UPDATE T2 SET ID = MOD(ID, 17) + 1;

已更新97行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON EXP
SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME)
2 INDEX(T2 IND_T2_NAME) USE_NL(T1, T2) */
3 T1.ID, T1.NAME, T2.NAME
4 FROM T1, T2
5 WHERE T1.ID = T2.ID
6 ORDER BY T1.NAME
7 ;

ID NAME NAME
---------- ------------------------------ ------------------------------
5 BONUS CHAINED_ROWS
5 BONUS P_DEPT
5 BONUS P_TEST3
5 BONUS SYS_C0012194
5 BONUS SYS_LOB0000071259C00002$$
5 BONUS T_DUMP
2 CHAINED_ROWS NUM_TO_BIN
2 CHAINED_ROWS P_DEFINE_TYPE2
2 CHAINED_ROWS T_BITMAP
2 CHAINED_ROWS T_TEST3
2 CHAINED_ROWS T_TEST4
2 CHAINED_ROWS multi_sum
3 DEPT DR$IND_T_DOCS$I
3 DEPT F_MAX_SAL
3 DEPT F_MULTI_SUM_JAVA
3 DEPT IND_B_T_OBJECT_TYPE
3 DEPT SYS_IOT_TOP_71257
3 DEPT T_TEST4
.
.
.
10 T_NOSORT EMP
10 T_NOSORT F_ADD_STR
10 T_NOSORT F_GETSEQ
10 T_NOSORT F_RETURN_INTERVAL
10 T_NOSORT SEQ_MY_REC
10 T_NOSORT TO_BLOB
11 T_OTHER BONUS
11 T_OTHER F_TEST
11 T_OTHER IND_EMP_DEPTNO
11 T_OTHER P_PARTITION_TOTAL
11 T_OTHER T_NOSORT
11 T_OTHER sum_multi

已选择97行。

执行计划
----------------------------------------------------------
Plan hash value: 3719138605

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 5820 |
|* 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |
| 2 | NESTED LOOPS | | 97 | 5820 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
| 5 | INDEX FULL SCAN | IND_T2_NAME | 97 | |
---------------------------------------------------------------------

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

1 - filter("T1"."ID"="T2"."ID")

SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME)
2 INDEX_DESC(T2 IND_T2_NAME) USE_NL(T1, T2) */
3 T1.ID, T1.NAME, T2.NAME
4 FROM T1, T2
5 WHERE T1.ID = T2.ID
6 ORDER BY T1.NAME
7 ;

ID NAME NAME
---------- ------------------------------ ------------------------------
5 BONUS T_DUMP
5 BONUS SYS_LOB0000071259C00002$$
5 BONUS SYS_C0012194
5 BONUS P_TEST3
5 BONUS P_DEPT
5 BONUS CHAINED_ROWS
2 CHAINED_ROWS multi_sum
2 CHAINED_ROWS T_TEST4
2 CHAINED_ROWS T_TEST3
2 CHAINED_ROWS T_BITMAP
2 CHAINED_ROWS P_DEFINE_TYPE2
2 CHAINED_ROWS NUM_TO_BIN
3 DEPT T_TEST4
3 DEPT SYS_IOT_TOP_71257
3 DEPT IND_B_T_OBJECT_TYPE
3 DEPT F_MULTI_SUM_JAVA
3 DEPT F_MAX_SAL
3 DEPT DR$IND_T_DOCS$I
.
.
.
10 T_NOSORT TO_BLOB
10 T_NOSORT SEQ_MY_REC
10 T_NOSORT F_RETURN_INTERVAL
10 T_NOSORT F_GETSEQ
10 T_NOSORT F_ADD_STR
10 T_NOSORT EMP
11 T_OTHER sum_multi
11 T_OTHER T_NOSORT
11 T_OTHER P_PARTITION_TOTAL
11 T_OTHER IND_EMP_DEPTNO
11 T_OTHER F_TEST
11 T_OTHER BONUS

已选择97行。

执行计划
----------------------------------------------------------
Plan hash value: 2531946081

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 5820 |
|* 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |
| 2 | NESTED LOOPS | | 97 | 5820 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
| 5 | INDEX FULL SCAN DESCENDING | IND_T2_NAME | 97 | |
---------------------------------------------------------------------

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

1 - filter("T1"."ID"="T2"."ID")

上面两个查询并没有指定对T2的NAME列进行排序，但是通过索引的全扫描和NESTED LOOP的特性，以及得到了先对T1的NAME排序，然后对T2的NAME进行排序的查询结果。

可惜的是，Oracle的执行计划还没有那么的聪明，并没有认识到这种情况下可以省略对T2的NAME的排序，因此，如果真的在ORDER BY语句中增加T2.NAME，那么Oracle仍然会去进行排序的：

SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME)
2 INDEX(T2 IND_T2_NAME) USE_NL(T1, T2) */
3 T1.ID, T1.NAME, T2.NAME
4 FROM T1, T2
5 WHERE T1.ID = T2.ID
6 ORDER BY T1.NAME, T2.NAME DESC
7 ;

ID NAME NAME
---------- ------------------------------ ----------------------------
5 BONUS T_DUMP
5 BONUS SYS_LOB0000071259C00002$$
5 BONUS SYS_C0012194
5 BONUS P_TEST3
5 BONUS P_DEPT
5 BONUS CHAINED_ROWS
2 CHAINED_ROWS multi_sum
2 CHAINED_ROWS T_TEST4
2 CHAINED_ROWS T_TEST3
2 CHAINED_ROWS T_BITMAP
2 CHAINED_ROWS P_DEFINE_TYPE2
2 CHAINED_ROWS NUM_TO_BIN
3 DEPT T_TEST4
3 DEPT SYS_IOT_TOP_71257
3 DEPT IND_B_T_OBJECT_TYPE
3 DEPT F_MULTI_SUM_JAVA
3 DEPT F_MAX_SAL
3 DEPT DR$IND_T_DOCS$I
.
.
.
10 T_NOSORT TO_BLOB
10 T_NOSORT SEQ_MY_REC
10 T_NOSORT F_RETURN_INTERVAL
10 T_NOSORT F_GETSEQ
10 T_NOSORT F_ADD_STR
10 T_NOSORT EMP
11 T_OTHER sum_multi
11 T_OTHER T_NOSORT
11 T_OTHER P_PARTITION_TOTAL
11 T_OTHER IND_EMP_DEPTNO
11 T_OTHER F_TEST
11 T_OTHER BONUS

已选择97行。

执行计划
----------------------------------------------------------
Plan hash value: 1438746903

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 5820 |
| 1 | SORT ORDER BY | | 97 | 5820 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |
| 3 | NESTED LOOPS | | 97 | 5820 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
| 5 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
| 6 | INDEX FULL SCAN | IND_T2_NAME | 97 | |
----------------------------------------------------------------------

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

2 - filter("T1"."ID"="T2"."ID")

上面通过NESTED LOOP来保证连接结果集的顺序是有条件的，它是建立在驱动表获取的数据是排序的这个基础之上的。也就是说，这篇文章的内容是建立在上一篇文章的基础之上。因此上一篇文章中的限制同样会影响这篇文章中的内容。

比如说索引不存储NULL值情况。对于可空字段的排序，是无法利用索引全扫描的（当然，是可以利用索引范围扫描的，不过前提是对这个字段进行了条件限制），因此这种情况就无法利用索引的全扫描来保证获取记录的顺序。

最后仍然想要强调的是，这里只是讨论避免排序操作的可能性。并不意味着避免了排序就会带来查询性能的提升。而且为了避免排序，必须保证查询的执行计划是确定的，所以必须使用大量的HINT来固定执行计划，不但对HINT有要求，对查询语句的编写也是有要求的。