共计 9043 个字符,预计需要花费 23 分钟才能阅读完成。
在 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")
更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12
:
正文完
星哥玩云-微信公众号