跟着官方文档学HINT（六）连接顺序

最新推荐文章于 2021-04-05 22:22:21 发布

道行尚浅

最新推荐文章于 2021-04-05 22:22:21 发布

阅读量840

点赞数

分类专栏： ORACLE优化文章标签： Oracle hint sql提示

本文链接：https://blog.csdn.net/daohengshangqian/article/details/40747497

版权

ORACLE优化专栏收录该内容

39 篇文章 0 订阅

订阅专栏

本文通过示例详细介绍了Oracle中的LEADING和ORDERED提示如何影响SQL查询的执行计划，展示了不同提示下连接顺序对性能的影响，强调了正确使用HINT优化SQL的重要性。

摘要由CSDN通过智能技术生成

--LEADING 指定生成的执行计划的驱动表
SQL> SELECT *
  2    FROM DEPT D, DAO_OBJECT1 DO, EMP E
  3   WHERE D.DEPTNO = DO.OBJECT_ID
  4     AND E.DEPTNO = D.DEPTNO;


14 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1036164400


----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    14 |  2170 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN                    |              |    14 |  2170 |    13   (8)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     4 |   468 |    10   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL         | DEPT         |     4 |    80 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_DAO_OBJ1 |     1 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1  |     1 |    97 |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | EMP          |    14 |   532 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


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


   1 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - access("D"."DEPTNO"="DO"."OBJECT_ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       3560  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed


SQL> SELECT /*+ LEADING(DO)*/*
  2    FROM DEPT D, DAO_OBJECT1 DO, EMP E
  3   WHERE D.DEPTNO = DO.OBJECT_ID
  4     AND E.DEPTNO = D.DEPTNO;


14 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2813041849


----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    14 |  2170 |   305   (2)| 00:00:04 |
|*  1 |  HASH JOIN                    |              |    14 |  2170 |   305   (2)| 00:00:04 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     4 |   468 |   303   (2)| 00:00:04 |
|   4 |     TABLE ACCESS FULL         | DAO_OBJECT1  | 75500 |  7151K|   295   (1)| 00:00:04 |
|*  5 |     INDEX RANGE SCAN          | IDX_DAO_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | EMP          |    14 |   532 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


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


   1 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - access("D"."DEPTNO"="DO"."OBJECT_ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1083  consistent gets
       1074  physical reads
          0  redo size
       3560  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed




SQL> SELECT *
  2    FROM DEPT D, DAO_OBJECT1 DO, EMP E
  3   WHERE D.DEPTNO = DO.OBJECT_ID
  4     AND E.DEPTNO = D.DEPTNO;


14 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1036164400


----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    14 |  2170 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN                    |              |    14 |  2170 |    13   (8)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     4 |   468 |    10   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL         | DEPT         |     4 |    80 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_DAO_OBJ1 |     1 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1  |     1 |    97 |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | EMP          |    14 |   532 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


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


   1 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - access("D"."DEPTNO"="DO"."OBJECT_ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       3560  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed


--ORDERED 建议优化器按FROM 后表出现的顺序进行表连接


SQL> SELECT /*+ ORDERED */*
  2    FROM DAO_OBJECT1 DO, DEPT D,  EMP E
  3   WHERE D.DEPTNO = DO.OBJECT_ID
  4     AND E.DEPTNO = D.DEPTNO;


14 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2813041849


----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    14 |  2170 |   305   (2)| 00:00:04 |
|*  1 |  HASH JOIN                    |              |    14 |  2170 |   305   (2)| 00:00:04 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     4 |   468 |   303   (2)| 00:00:04 |
|   4 |     TABLE ACCESS FULL         | DAO_OBJECT1  | 75500 |  7151K|   295   (1)| 00:00:04 |
|*  5 |     INDEX RANGE SCAN          | IDX_DAO_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT         |     1 |    20 |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | EMP          |    14 |   532 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


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


   1 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - access("D"."DEPTNO"="DO"."OBJECT_ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1083  consistent gets
       1074  physical reads
          0  redo size
       3560  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed