手机版
你好,游客 登录 注册
背景:
阅读新闻

Oracle 利用执行计划来避免排序操作

[日期:2019-03-28] 来源:Linux社区  作者:yhq1314 [字体: ]

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

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2019-03/157758.htm

linux
相关资讯       Oracle执行计划  Oracle排序 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款