利用执行计划来避免排序操作（二）

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

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

:^"e/g.|c.C`O0

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

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
4J)F8VZt{0SQL> SELECT /*+ USE_MERGE(T1, T2) */ T2.NAME, T1.IDITPUB个人空间(Z6e"{oG3n
2 FROM T1, T2ITPUB个人空间SMB)?)g2Am5AI
3 WHERE T1.NAME = T2.NAME
)FJKxsI0 4 ;

NAME ID
T,Z2Gg-Z6kcDN0------------------------------ ----------
$U D8h%E)ObeWC[0BONUS 5ITPUB个人空间^:|@*Z9?!n%r+e
CHAINED_ROWS 2ITPUB个人空间m#]3R t#h)PJ ucD
DEPT 3
d+o%j.Xr0DR$IND_T_DOCS$I 1
l0g,sn7Sg
@r7^V0DR$IND_T_DOCS$K 17
;D6bY*oWi3f9d0DR$IND_T_DOCS$N 16
r KL
r@0O0DR$IND_T_DOCS$R 9ITPUB个人空间$W4WnH6]dR@'a
DUMMY 7ITPUB个人空间(px!b8x
z#{
EMP 4ITPUB个人空间!\,n0Ql:vE
SALGRADE 6ITPUB个人空间0C@I5wQ%f.|
T 8ITPUB个人空间n ^ZF1f%JQS
T1 14
)i
I
zy9` D:rU0T2 15ITPUB个人空间8}(KeU?,R8~R
T_BITMAP 13
7\'otp;Jm0T_DUMP 12
Emg9GA&OE0T_NOSORT 10
%@*k,BDM[0T_OTHER 11

已选择17行。

执行计划
m6V8q9b&_0----------------------------------------------------

x2~x l h]0Plan hash value: 412793182

----------------------------------------------------
NBv%XhhAh0| Id | Operation | Name | Rows | Bytes |
'^'ksOT!}
E0----------------------------------------------------
]]%]p!A4tIU*J5`0| 0 | SELECT STATEMENT | | 19 | 893 |ITPUB个人空间fZyGSouY
| 1 | MERGE JOIN | | 19 | 893 |
9A4X HoD0| 2 | SORT JOIN | | 17 | 510 |ITPUB个人空间2qwvh"TaCg2cH([:g7]
| 3 | TABLE ACCESS FULL| T1 | 17 | 510 |
H&]'Y vj)UH)DR0|* 4 | SORT JOIN | | 97 | 1649 |
#z}$eH3c0| 5 | TABLE ACCESS FULL| T2 | 97 | 1649 |
'|bfx
ZS7i~9Y6o0----------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间[{T&_4dW:yP \c
---------------------------------------------------

4 - access("T1"."NAME"="T2"."NAME")ITPUB个人空间,[TN9{,M6h
im6~
filter("T1"."NAME"="T2"."NAME")

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

SQL> SELECT /*+ USE_MERGE(T2, T1) */ T2.NAME, T1.IDITPUB个人空间2N!|2v(b3Z.| ],Z
2 FROM T1, T2
Du(a)ci9Z%l0 3 WHERE T1.NAME = T2.NAMEITPUB个人空间([?v%o6@&K
4 ORDER BY T2.NAME
D7fy3s.I#p.AX8X m0 5 ;

NAME IDITPUB个人空间Z Oy?eX%I1W'X8m
------------------------------ ----------
Ob3e h
A`0BONUS 5ITPUB个人空间(A2u[y5gG
CHAINED_ROWS 2ITPUB个人空间Pqyw*o&Xh4B
DEPT 3
/~4o"l&HC!SaIGo y0DR$IND_T_DOCS$I 1
q!BbIR@3my`0DR$IND_T_DOCS$K 17ITPUB个人空间g"y[6x6f
h
DR$IND_T_DOCS$N 16
4M"[5@5La0DR$IND_T_DOCS$R 9ITPUB个人空间Q3N+r1t
|\q%Gz
DUMMY 7ITPUB个人空间Q K+LE7V*P%p
EMP 4ITPUB个人空间,h&Z$v;e[h
SALGRADE 6ITPUB个人空间B;[mXQp+f/T6k0M
T 8ITPUB个人空间.Eb#Uw3vamJ
T1 14
J$Y:|)e$p"^6nkZ0T2 15
/H)D#s KC0l0T_BITMAP 13
m/|!W.oG8M&R0T_DUMP 12ITPUB个人空间S(h6cU#Kx$@1B
T_NOSORT 10
9rw@n)ta9it,J0T_OTHER 11

已选择17行。

执行计划ITPUB个人空间9Wx4bpE!s8E~9`
----------------------------------------------------
#]Z5s1YB h7Qr0Plan hash value: 412793182

----------------------------------------------------ITPUB个人空间2`wNWq
| Id | Operation | Name | Rows | Bytes |ITPUB个人空间+G0[4I&G@:`9LV? {Rk
----------------------------------------------------ITPUB个人空间&T;S0p4Rok4f)c
| 0 | SELECT STATEMENT | | 19 | 893 |ITPUB个人空间WE2HW]z'b
| 1 | MERGE JOIN | | 19 | 893 |ITPUB个人空间7V&kOB'u~(z|
| 2 | SORT JOIN | | 17 | 510 |
\e1Qo!s;LO3nZl0| 3 | TABLE ACCESS FULL| T1 | 17 | 510 |
TiK\(m\0|* 4 | SORT JOIN | | 97 | 1649 |ITPUB个人空间LY$Xb2r'O"}
| 5 | TABLE ACCESS FULL| T2 | 97 | 1649 |
]L-B(N)y0----------------------------------------------------

Predicate Information (identified by operation id):
/r"M!E T1b~7{0---------------------------------------------------

4 - access("T1"."NAME"="T2"."NAME")
}az:J(oV)zvS4Tg0 filter("T1"."NAME"="T2"."NAME")

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

SQL> SELECT /*+ USE_MERGE(T2, T1) */ T2.NAME, T1.IDITPUB个人空间r-yu;~E-b2SaD\
2 FROM T1, T2
'X5Gf'B4t7k0 3 WHERE T1.NAME = T2.NAME
LbH|w y0 4 ORDER BY T2.NAME DESC
d9C d1b`^)x0 5 ;

NAME ID
/aWq:oP0------------------------------ ----------
/o,b n9k?R#N3IW0T_OTHER 11ITPUB个人空间Q{Cl4rP0o @
T_NOSORT 10
?QOSk7ffg9h0T_DUMP 12
N@ G#I7Q0T_BITMAP 13ITPUB个人空间j.`"BE'Ap;|RH
T2 15
5A(AX*x;[?u0T1 14ITPUB个人空间 K8@4D6MrR!@
T 8ITPUB个人空间0xg"Zn2lzT#uJ1c
SALGRADE 6ITPUB个人空间:h"b"`.k6Bg#h
EMP 4ITPUB个人空间qI)^8PN"k.Z-e4U@)d
DUMMY 7
"F V.C0Y4gD,^0DR$IND_T_DOCS$R 9ITPUB个人空间w_$W5e$U3UMng/]
DR$IND_T_DOCS$N 16
sN,cwW|/m%s#M0DR$IND_T_DOCS$K 17
Ey#G,HJ&g M9I?0DR$IND_T_DOCS$I 1ITPUB个人空间 A:j
A%Qv%TMqR&V
DEPT 3ITPUB个人空间J,T/{8mC9JT
CHAINED_ROWS 2
$[#Euh\+s-b#v0BONUS 5

已选择17行。

执行计划ITPUB个人空间QIa#@4V+r]/n_;OH
-----------------------------------------------------
6i4OL1bI"a0Plan hash value: 2216793107

-----------------------------------------------------
)JP'}9obo9k0| Id | Operation | Name | Rows | Bytes |ITPUB个人空间O TZC!q
-----------------------------------------------------ITPUB个人空间e2R~'d3LeE
| 0 | SELECT STATEMENT | | 19 | 893 |ITPUB个人空间N2@rg9` VOa|e
| 1 | SORT ORDER BY | | 19 | 893 |ITPUB个人空间?!K+vu`|%e,W
| 2 | MERGE JOIN | | 19 | 893 |
e,?{3} L0| 3 | SORT JOIN | | 17 | 510 |
_q!]:D+R0| 4 | TABLE ACCESS FULL| T1 | 17 | 510 |
OQA T ^
g s7}0|* 5 | SORT JOIN | | 97 | 1649 |ITPUB个人空间#E5lqv0blH Q
| 6 | TABLE ACCESS FULL| T2 | 97 | 1649 |
p)oTx7L
qD$L0-----------------------------------------------------

Predicate Information (identified by operation id):
S(b!h W$],z0---------------------------------------------------

5 - access("T1"."NAME"="T2"."NAME")ITPUB个人空间
F*`8N ~%j+F9rH(CB7r
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 ITPUB个人空间buo([S8N1| r
2 FROM T1, T2
Y#c0ap&v0 3 WHERE T1.ID = T2.IDITPUB个人空间8o
wl,ASn-aO%ov
4 ;

ID NAME NAME

@x?I_DJp0---------- ------------------------------ ----------------------------ITPUB个人空间*[J3n$L0fhd4H
1 DR$IND_T_DOCS$I P_DEFINE_TYPE2
e*O:iI0^ @N0 2 CHAINED_ROWS DR$IND_T_DOCS$IITPUB个人空间dE~$q'Z
h
E$K;?
3 DEPT P_TEST2
,mP:Q+k"cs?%q0 4 EMP CHAINED_ROWSITPUB个人空间!Jw+e9V&T@s'g
5 BONUS T_TEST1
is{$V]*`+M#R5h0 6 SALGRADE T_TEST1
k\}0F&s0 7 DUMMY P_TEST1
1qr}E1y{r0 8 T DEPT
N#SlRL0L0 9 DR$IND_T_DOCS$R EMPITPUB个人空间 @i:uF7z;@5Y,b
10 T_NOSORT BONUS
1Ad%f/}9}0 11 T_OTHER SALGRADE
DI|2VXni
F0 12 T_DUMP DUMMY
qL JP8Z4wH Ms0 13 T_BITMAP T
w)AC1IYzt0 14 T1 IND_T_DOCS
wf{c zR%H&g0 15 T2 SYS_LOB0000071254C00006$$
q,C+?1cW0 16 DR$IND_T_DOCS$N DR$IND_T_DOCS$KITPUB个人空间2FA Koa)zNo
17 DR$IND_T_DOCS$K T_TEST3

已选择17行。

执行计划
)z&U8f*xsXf%M.n6Cr0----------------------------------------------------------ITPUB个人空间:lDi ca i8DTS
Plan hash value: 3621112097

----------------------------------------------------------------------
3sA#L5|3{/L*n0| Id | Operation | Name | Rows | Bytes | Cost
Si+u_P5e C!E0----------------------------------------------------------------------
9n"w f x3EYq1A3P0| 0 | SELECT STATEMENT | | 17 | 1020 |
o0XpYw&Z!Zi0| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 |
{z+R] E;SBKJRFR0| 2 | NESTED LOOPS | | 17 | 1020 | ITPUB个人空间,e!ef^:R)R
| 3 | TABLE ACCESS FULL | T1 | 17 | 510 |
G{(T.@r#y2Nq y0|* 4 | INDEX RANGE SCAN | IND_T2_ID | 1 | | ITPUB个人空间8nut7_BS/I
----------------------------------------------------------------------

Predicate Information (identified by operation id):
"y1^b&oU,h4Dm [0---------------------------------------------------

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) */ ITPUB个人空间6PAX!? j-dCu
2 T1.ID, T1.NAME, T2.NAME ITPUB个人空间v^3`hdmn_
3 FROM T1, T2ITPUB个人空间&`mB5b#b#BP
4 WHERE T1.ID = T2.ID
*B9U!~-Af,BA0 5 ORDER BY T1.NAME
|\GP#GB:[0 6 ;

ID NAME NAME
w\g6w r0---------- ------------------------------ ------------------------------ITPUB个人空间 kUcLQ,Af3o$~~+W
5 BONUS T_TEST1ITPUB个人空间;epo,j[
2 CHAINED_ROWS DR$IND_T_DOCS$I
k3Tg"^*~0 3 DEPT P_TEST2
(U#akt6l#s9lDq ]0 1 DR$IND_T_DOCS$I P_DEFINE_TYPE2
N]!GI&d$m,a8XRn0 17 DR$IND_T_DOCS$K T_TEST3ITPUB个人空间JX ?t4\uN}7b
16 DR$IND_T_DOCS$N DR$IND_T_DOCS$K
2lt2fwN{0 9 DR$IND_T_DOCS$R EMPITPUB个人空间z'd_ }Q2nEpL
7 DUMMY P_TEST1
'b0vK*u8T)fq0 4 EMP CHAINED_ROWS
h+y[sv0 6 SALGRADE T_TEST1ITPUB个人空间'FTd*`!H"\6oW
8 T DEPTITPUB个人空间8Kmc;p2qlm,i:_
14 T1 IND_T_DOCSITPUB个人空间-}x]V/p"Q^H5h
15 T2 SYS_LOB0000071254C00006$$
5rYh0M)DbA0 13 T_BITMAP TITPUB个人空间k
aR+{z(^a
12 T_DUMP DUMMY
lDP5P"h0 10 T_NOSORT BONUSITPUB个人空间*w#u&y;M;? ~[}
11 T_OTHER SALGRADE

已选择17行。

执行计划ITPUB个人空间(zk-V-j1LrP9y
----------------------------------------------------------
F'O}J?zI k7@!C0Plan hash value: 1062594094

---------------------------------------------------------------------
mSI8z*`sz0| Id | Operation | Name | Rows | Bytes |
of.?\Vx0---------------------------------------------------------------------ITPUB个人空间 tE'A~3y8cq)Fo+Vc)|
| 0 | SELECT STATEMENT | | 17 | 1020 |ITPUB个人空间2K+T/}
y;zn*nY
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 30 |ITPUB个人空间0]U]:I-uhn{n
| 2 | NESTED LOOPS | | 17 | 1020 |
a$U(kO6[,q#w&v}0| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
kf9JeW2{,x,?)z0| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
U1ysJ]s0|* 5 | INDEX RANGE SCAN | IND_T2_ID | 1 | |
6lq#JE;Zk1Av
\0---------------------------------------------------------------------

Predicate Information (identified by operation id):
$oD
sEaD8I0---------------------------------------------------

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 OFFITPUB个人空间9qA
B W7v m,C
SQL> UPDATE T2 SET ID = MOD(ID, 17) + 1;

已更新97行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON EXPITPUB个人空间2P8ZaqVH:u2PD
SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME) ITPUB个人空间']g z-a5b|7k
2 INDEX(T2 IND_T2_NAME) USE_NL(T1, T2) */
wTLY'Gr9|0 3 T1.ID, T1.NAME, T2.NAME ITPUB个人空间"l+k'C b'Oo@
4 FROM T1, T2ITPUB个人空间8L+`1T8q}6N
5 WHERE T1.ID = T2.ID
cG0[-x"^@{ b2p6T-\]0 6 ORDER BY T1.NAMEITPUB个人空间 K4A,^+xD?fR
7 ;

ID NAME NAMEITPUB个人空间^ a.p)u-XYN5kt
---------- ------------------------------ ------------------------------ITPUB个人空间I5[r"W*u `#qO9C
5 BONUS CHAINED_ROWSITPUB个人空间zA1TM$ts*[
y Z
5 BONUS P_DEPTITPUB个人空间B.U yK3b0g;m
5 BONUS P_TEST3
r)},{.z6Tl:jL s]q0 5 BONUS SYS_C0012194
E(X:_lIi0 5 BONUS SYS_LOB0000071259C00002$$ITPUB个人空间#_;A`O%KtqP]
5 BONUS T_DUMPITPUB个人空间8Iz&NY$d"M~0q.d
2 CHAINED_ROWS NUM_TO_BINITPUB个人空间5o,saQy)gp(T
2 CHAINED_ROWS P_DEFINE_TYPE2ITPUB个人空间``d*Qwo(y"WtG
2 CHAINED_ROWS T_BITMAP
R'N_s0n0 2 CHAINED_ROWS T_TEST3
!y:?-{{+k~0 2 CHAINED_ROWS T_TEST4
_X;U$Y9]k7x0 2 CHAINED_ROWS multi_sumITPUB个人空间 h,](_/b"c;n1Ku]d O
3 DEPT DR$IND_T_DOCS$I
6Z3fV'M%bV"D+Q |-H0 3 DEPT F_MAX_SAL
0Et4g4A4nj0 3 DEPT F_MULTI_SUM_JAVA
u"g,|c~TS;Z7yw0 3 DEPT IND_B_T_OBJECT_TYPEITPUB个人空间J|\ \e,qJ
3 DEPT SYS_IOT_TOP_71257
w[lKI'_7|0 3 DEPT T_TEST4ITPUB个人空间7F*Olm2m'mn:KM
.
Ts+}?V]$m({.[0.ITPUB个人空间 fT+G"NVPb
.ITPUB个人空间3|aEZURr3k~
10 T_NOSORT EMPITPUB个人空间]*gEzK(\;p
10 T_NOSORT F_ADD_STRITPUB个人空间S][5HK~F,\H
10 T_NOSORT F_GETSEQITPUB个人空间 Rxg3T0Q+vs
10 T_NOSORT F_RETURN_INTERVALITPUB个人空间&f5rLc'_X LZ"d/U
10 T_NOSORT SEQ_MY_REC
4X1yr#X%_k0 10 T_NOSORT TO_BLOBITPUB个人空间b^ bBQXnH8l*x/_
11 T_OTHER BONUSITPUB个人空间cv"J`!Ji#A
11 T_OTHER F_TESTITPUB个人空间j
d8PN"^9G(|5^y
11 T_OTHER IND_EMP_DEPTNOITPUB个人空间k7zNhX5J6L:Jd
11 T_OTHER P_PARTITION_TOTALITPUB个人空间\M2b-~/snpX
11 T_OTHER T_NOSORTITPUB个人空间Q(qi5Y7G t^
11 T_OTHER sum_multi

已选择97行。

执行计划ITPUB个人空间8Abls;vm7v E7o
----------------------------------------------------------
_T8s,n(pf_0Plan hash value: 3719138605

---------------------------------------------------------------------
9}[}&X:x0| Id | Operation | Name | Rows | Bytes |ITPUB个人空间U"S*l Xr+A
---------------------------------------------------------------------
R2gH_:mv%[0K&wK0| 0 | SELECT STATEMENT | | 97 | 5820 |ITPUB个人空间0]}kj9]!D3F
|* 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |ITPUB个人空间5qLR@$[+Cs-d{n*f2W
| 2 | NESTED LOOPS | | 97 | 5820 |
H$tad&~+z+?0| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |
*|%rz@/Ab1K0| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
N{#R,v7S$A*d0| 5 | INDEX FULL SCAN | IND_T2_NAME | 97 | |
{q ]f r:u0---------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间U!lSh-| w
---------------------------------------------------

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

SQL> SELECT /*+ ORDERED INDEX(T1 IND_T1_NAME)
}N1R%W8J~a['A F0 2 INDEX_DESC(T2 IND_T2_NAME) USE_NL(T1, T2) */
6e1a3Hi/P#r5bj0 3 T1.ID, T1.NAME, T2.NAME ITPUB个人空间'OS%[-^sW`.}
4 FROM T1, T2ITPUB个人空间3k(sX7C;|z%];zz
5 WHERE T1.ID = T2.ID
*t1L8Xe+QK0 6 ORDER BY T1.NAME
5KF*W+fhT0 7 ;

ID NAME NAME
Fm$m3q.I!{5aE0---------- ------------------------------ ------------------------------
1}aIz)M9w|c M0 5 BONUS T_DUMPITPUB个人空间3A:X0]Ie/[-j,q T
5 BONUS SYS_LOB0000071259C00002$$
R6c8sE-c/NR {i0D0 5 BONUS SYS_C0012194
jQ#SBF @0 5 BONUS P_TEST3
(o
[L-uN5q1[0 5 BONUS P_DEPT
s!A7gl*a0 5 BONUS CHAINED_ROWSITPUB个人空间:dbs\E/u!_
2 CHAINED_ROWS multi_sum
mm4Y5G2t W0 2 CHAINED_ROWS T_TEST4ITPUB个人空间+GS"e.aq([0U"ZZ
2 CHAINED_ROWS T_TEST3
0kJY^:A0Q0 2 CHAINED_ROWS T_BITMAPITPUB个人空间 fHK*`xA is.T
2 CHAINED_ROWS P_DEFINE_TYPE2
"O?4e5I:?0 2 CHAINED_ROWS NUM_TO_BIN
1y%t:Yi%H'Y
U RT0 3 DEPT T_TEST4
IY} CG`#?1z$K0 3 DEPT SYS_IOT_TOP_71257ITPUB个人空间V%Pcq'G%FA%pVw)l
3 DEPT IND_B_T_OBJECT_TYPEITPUB个人空间J8OU6xK
t
3 DEPT F_MULTI_SUM_JAVA
E\ e)b9N HJA0 3 DEPT F_MAX_SAL
WKo"_1zd2| jo0 3 DEPT DR$IND_T_DOCS$I
:`*@,W@uu;]0.ITPUB个人空间5mDP+Nz4R6p
.
0I!^6DY-mMt#D#Fb(k0.
&_
\)L2gt9v*U0 10 T_NOSORT TO_BLOBITPUB个人空间g|5jU"O
u
10 T_NOSORT SEQ_MY_REC
o9H^#|2r2U0 10 T_NOSORT F_RETURN_INTERVALITPUB个人空间;wZg*Cm#n5fG
10 T_NOSORT F_GETSEQITPUB个人空间8u&@&j!M"Bk&d
10 T_NOSORT F_ADD_STRITPUB个人空间+]F T%d#y\K8e.S;l'Y
10 T_NOSORT EMPITPUB个人空间-kO;q#P!~$DgSB9e
11 T_OTHER sum_multiITPUB个人空间 u+C'i%bUR7q3T%P-z
11 T_OTHER T_NOSORT
L\5t&Eq0 11 T_OTHER P_PARTITION_TOTAL
+Oh&q a9}]k``0 11 T_OTHER IND_EMP_DEPTNO
`8}pSER:om0 11 T_OTHER F_TEST
br1`t }*L A7q0 11 T_OTHER BONUS

已选择97行。

执行计划ITPUB个人空间+x'J#NP+W6qo
----------------------------------------------------------ITPUB个人空间\2gM)|+M]
Plan hash value: 2531946081

---------------------------------------------------------------------ITPUB个人空间 m+Wa D8F(HY6xk
| Id | Operation | Name | Rows | Bytes |ITPUB个人空间Xm3V~$a Vn+j e8X
---------------------------------------------------------------------ITPUB个人空间[
nT[1I~
| 0 | SELECT STATEMENT | | 97 | 5820 |
{-d9{ B*q ]\$MwK0|* 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |
!FjAw6Q0| 2 | NESTED LOOPS | | 97 | 5820 |ITPUB个人空间8T^x3d Z:[X\&PH
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |ITPUB个人空间!Pc KB X9C|F
| 4 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
1_)e;F6}wu+NUW*P0| 5 | INDEX FULL SCAN DESCENDING | IND_T2_NAME | 97 | |
PfQye k1\0---------------------------------------------------------------------

Predicate Information (identified by operation id):
@#K(C$R'|R!x3n]0---------------------------------------------------

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)
IuF^J6iWR0 2 INDEX(T2 IND_T2_NAME) USE_NL(T1, T2) */ ITPUB个人空间)J qT(\/S#`
3 T1.ID, T1.NAME, T2.NAME ITPUB个人空间x \
s0dE4C#Q
4 FROM T1, T2ITPUB个人空间Y,fq?&@
\'X
kF@0fd
5 WHERE T1.ID = T2.IDITPUB个人空间C arV!`:L
6 ORDER BY T1.NAME, T2.NAME DESC
GzV!{nc.~v0 7 ;

ID NAME NAMEITPUB个人空间6bS1QF*i
---------- ------------------------------ ----------------------------ITPUB个人空间g | BE*KF8^2zY&?!J
5 BONUS T_DUMP
D1g O?[RA(x.A0 5 BONUS SYS_LOB0000071259C00002$$
7zz+Rc!jw8wH{0 5 BONUS SYS_C0012194
1K u%BVN)nn"G-e0 5 BONUS P_TEST3ITPUB个人空间0P:lV1MU;r
^*HD
5 BONUS P_DEPTITPUB个人空间??fd^ G
5 BONUS CHAINED_ROWSITPUB个人空间"r,P3Y/G:V?G?
2 CHAINED_ROWS multi_sumITPUB个人空间"bA4ZEs#p.O2S_
2 CHAINED_ROWS T_TEST4ITPUB个人空间 I8L cZB
2 CHAINED_ROWS T_TEST3
_|+xZC'N&i0 2 CHAINED_ROWS T_BITMAPITPUB个人空间|XOQ'l S E}
2 CHAINED_ROWS P_DEFINE_TYPE2
7VJkbZA0 2 CHAINED_ROWS NUM_TO_BIN
x*V.E zg2K0 3 DEPT T_TEST4
7e`!y*j H7w0 3 DEPT SYS_IOT_TOP_71257
`C j:g8dX,F0 3 DEPT IND_B_T_OBJECT_TYPEITPUB个人空间9_Q-sfQM[
3 DEPT F_MULTI_SUM_JAVAITPUB个人空间#I3s^#mo7s8^#B
3 DEPT F_MAX_SAL
&M,Iz$@'U aw0 3 DEPT DR$IND_T_DOCS$IITPUB个人空间"z|NeEi G8[^1r
.ITPUB个人空间4ZCE]6M W[5^
.ITPUB个人空间SZ3m)L^PP
.ITPUB个人空间IF7QR3|o p
10 T_NOSORT TO_BLOBITPUB个人空间#S0[%S%J.l)L*uAh%F
10 T_NOSORT SEQ_MY_REC
sk^XtAN0 10 T_NOSORT F_RETURN_INTERVALITPUB个人空间_4Y6z.n6X(w
}
u C6Z
10 T_NOSORT F_GETSEQ
*q h];AV`T0 10 T_NOSORT F_ADD_STR
b-A-edko\ Q0 10 T_NOSORT EMP
AE9R.MJ7mY0 11 T_OTHER sum_multi
+v1}l-o4M!L$vG0 11 T_OTHER T_NOSORTITPUB个人空间BfW.~?B(N,f*_
11 T_OTHER P_PARTITION_TOTAL
+J;y(`d[U6]C0 11 T_OTHER IND_EMP_DEPTNOITPUB个人空间r J$R)np+E+|
11 T_OTHER F_TEST
v+Q JAS*k'ho K9P4E7l0 11 T_OTHER BONUS

已选择97行。

执行计划
0i/CEyXi@kW)Z:x0----------------------------------------------------------
v){B'`2TN[0Plan hash value: 1438746903

----------------------------------------------------------------------
h8\"A}
unlw9~0| Id | Operation | Name | Rows | Bytes |ITPUB个人空间sv'Q\1}|:MoJ/h
----------------------------------------------------------------------
n'c wv4L9^0| 0 | SELECT STATEMENT | | 97 | 5820 |
D2h&m(Vv/@;}8[0| 1 | SORT ORDER BY | | 97 | 5820 |ITPUB个人空间b3f:Giw,L
j
|* 2 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 |ITPUB个人空间YYe#\G)]mp,cl
| 3 | NESTED LOOPS | | 97 | 5820 |ITPUB个人空间k9I YV `"dy \
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 17 | 510 |ITPUB个人空间u&S H}1yz*{
| 5 | INDEX FULL SCAN | IND_T1_NAME | 17 | |
.]9W;g;Era7U|0| 6 | INDEX FULL SCAN | IND_T2_NAME | 97 | |ITPUB个人空间EP$Rd] P `'?D[
----------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间$zor!x k.QV@1`
---------------------------------------------------

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

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

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

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