Oracle 利用执行计划来避免排序操作
在oracle中，利用index来避免排序
SQL> CREATE TABLE T_NOSORT (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL); SQL> CREATE INDEX IND_T_NOSORT_NAME ON T_NOSORT(NAME); SQL> INSERT INTO T_NOSORT SELECT ROWNUM, TABLE_NAME FROM USER_TABLES; SQL> COMMIT; SQL> SET AUTOT ON EXP SQL> SELECT ID, NAME FROM T_NOSORT ORDER BY NAME; ---------------------------------------------------------- Plan hash value: 1041838668 ------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 450 | | 1 | SORT ORDER BY | | 15 | 450 | | 2 | TABLE ACCESS FULL| T_NOSORT | 15 | 450 | ------------------------------------------------------- QL> SELECT /*+ INDEX(T_NOSORT IND_T_NOSORT_NAME) */ ID, NAME FROM T_NOSORT ORDER BY NAME; | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 41 | 1230 | 827 (1)| 00:00:10 | | 1 | TABLE ACCESS BY INDEX ROWID| T_NOSORT | 41 | 1230 | 827 (1)| 00:00:10 | | 2 | INDEX FULL SCAN | IND_T_NOSORT_NAME | 41 | | 26 (0)| 00:00:01 |
利用索引范围扫描
SQL> SELECT ID, NAME FROM T_NOSORT WHERE NAME < 'I' ORDER BY NAME; ---------------------------------------------------------- Plan hash value: 1041838668 ------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 270 | | 1 | SORT ORDER BY | | 9 | 270 | |* 2 | TABLE ACCESS FULL| T_NOSORT | 9 | 270 | ------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("NAME"<'I') SQL> SELECT /*+ INDEX(T_NOSORT IND_T_NOSORT_NAME) */ ID, NAME 2 FROM T_NOSORT WHERE NAME < 'I' ORDER BY NAME; ---------------------------------------------------------- Plan hash value: 919790285 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 270 | | 1 | TABLE ACCESS BY INDEX ROWID| T_NOSORT | 9 | 270 | |* 2 | INDEX RANGE SCAN | IND_T_NOSORT_NAME | 9 | | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"<'I') filter("NAME"<'I')
如果是倒序排序
QL> SELECT /*+ INDEX_DESC(T_NOSORT IND_T_NOSORT_NAME) */ ID, NAME 2 FROM T_NOSORT ORDER BY NAME DESC; ---------------------------------------------------------- Plan hash value: 2858378269 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 450 | | 1 | TABLE ACCESS BY INDEX ROWID| T_NOSORT | 15 | 450 | | 2 | INDEX FULL SCAN DESCENDING| IND_T_NOSORT_NAME | 15 | | -------------------------------------------------------------------------
这里只是说明了能够避免排序的执行计划，但是不一定能提升性能
上面针对于单表
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; SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME FROM USER_OBJECTS; SQL> COMMIT; SQL> SET AUTOT ON EXP SQL> SELECT /*+ USE_MERGE(T1, T2) */ T2.NAME, T1.ID FROM T1, T2 WHERE T1.NAME = T2.NAME ; | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 41 | 1927 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN | | 41 | 1927 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 41 | 1230 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 41 | 1230 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 99 | 1683 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| T2 | 99 | 1683 | 3 (0)| 00:00:01 | ---------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."NAME"="T2"."NAME") filter("T1"."NAME"="T2"."NAME")
SQL> SELECT /*+ USE_MERGE(T2, T1) */ T2.NAME, T1.ID FROM T1, T2 WHERE T1.NAME = T2.NAME ORDER BY T2.NAME ; ---------------------------------------------------- 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 FROM T1, T2 WHERE T1.NAME = T2.NAME ORDER BY T2.NAME DESC; | 0 | SELECT STATEMENT | | 41 | 1927 | 9 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 41 | 1927 | 9 (34)| 00:00:01 | | 2 | MERGE JOIN | | 41 | 1927 | 8 (25)| 00:00:01 | | 3 | SORT JOIN | | 41 | 1230 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL| T1 | 41 | 1230 | 3 (0)| 00:00:01 | |* 5 | SORT JOIN | | 99 | 1683 | 4 (25)| 00:00:01 | | 6 | TABLE ACCESS FULL| T2 | 99 | 1683 | 3 (0)| 00:00:01 |5 - access("T1"."NAME"="T2"."NAME") filter("T1"."NAME"="T2"."NAME")
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 ; ---------------------------------------------------------- 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")
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 ; ---------------------------------------------------------- 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")
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; 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 ; ---------------------------------------------------------- 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 ; ---------------------------------------------------------- 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")
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 ; ---------------------------------------------------------- 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")
