右连接RIGHT JOIN Oracle SQL优化实例

    Consistent gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产生了一致性读。

 

1. SQL_ID='2s7gsktyny0yq'

代入绑定变量的值:
SELECT UHS.ID,
       UHS.ORG_ID,
       UHS.JOB_ID,
       UHS.HOLIDAY_SYSTEM_NAME,
       UHS.DOWN_FLAG,
       UHS.BEGIN_DATE,
       UHS.END_DATE,
       UHS.STATE,
       UHS.STATE_DATE,
       UHS.CREATE_DATE,
       UHS.OPER_TYPE,
       UHS.COMMENTS,
       UO.ORG_PATH_CODE
  FROM IOPR1.UOS_HOLIDAY_SYSTEM UHS
 RIGHT JOIN IOPR1.UOS_ORG UO ON UHS.ORG_ID = UO.ORG_ID
 WHERE UHS.STATE = '10A' START WITH UO.ORG_ID = 1899
CONNECT BY UO.ORG_ID = PRIOR UO.PARENT_ID
 ORDER BY UO.ORG_PATH_CODE DESC;
 
执行计划为:

Plan hash value: 3518211019

--------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |  4747 |   421K|    18  (12)| 00:00:01 |
|   1 |  SORT ORDER BY                    |                    |  4747 |   421K|    18  (12)| 00:00:01 |
|*  2 |   FILTER                          |                    |       |       |            |          |
|*  3 |    CONNECT BY WITH FILTERING      |                    |       |       |            |          |
|*  4 |     FILTER                        |                    |       |       |            |          |
|   5 |      COUNT                        |                    |       |       |            |          |
|   6 |       MERGE JOIN OUTER            |                    |  4747 |   421K|    17   (6)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| UOS_ORG            |  4747 |   171K|    13   (0)| 00:00:01 |
|   8 |         INDEX FULL SCAN           | PK_UOS_ORG         |  4747 |       |     1   (0)| 00:00:01 |
|*  9 |        SORT JOIN                  |                    |     1 |    54 |     4  (25)| 00:00:01 |
|  10 |         TABLE ACCESS FULL         | UOS_HOLIDAY_SYSTEM |     1 |    54 |     3   (0)| 00:00:01 |
|* 11 |     HASH JOIN                     |                    |       |       |            |          |
|  12 |      CONNECT BY PUMP              |                    |       |       |            |          |
|  13 |      COUNT                        |                    |       |       |            |          |
|  14 |       MERGE JOIN OUTER            |                    |  4747 |   421K|    17   (6)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID| UOS_ORG            |  4747 |   171K|    13   (0)| 00:00:01 |
|  16 |         INDEX FULL SCAN           | PK_UOS_ORG         |  4747 |       |     1   (0)| 00:00:01 |
|* 17 |        SORT JOIN                  |                    |     1 |    54 |     4  (25)| 00:00:01 |
|  18 |         TABLE ACCESS FULL         | UOS_HOLIDAY_SYSTEM |     1 |    54 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   2 - filter("UHS"."STATE"='10A')
   3 - access("UO"."ORG_ID"=PRIOR "UO"."PARENT_ID")
   4 - filter("UO"."ORG_ID"=1899)
   9 - access("UHS"."ORG_ID"(+)="UO"."ORG_ID")
       filter("UHS"."ORG_ID"(+)="UO"."ORG_ID")
  11 - access("UO"."ORG_ID"=PRIOR "UO"."PARENT_ID")
  17 - access("UHS"."ORG_ID"(+)="UO"."ORG_ID")
       filter("UHS"."ORG_ID"(+)="UO"."ORG_ID")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2720  consistent gets
          0  physical reads
          0  redo size
       1129  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
         
== 建议改为如下语句：

SELECT UHS.ID,
       UHS.ORG_ID,
       UHS.JOB_ID,
       UHS.HOLIDAY_SYSTEM_NAME,
       UHS.DOWN_FLAG,
       UHS.BEGIN_DATE,
       UHS.END_DATE,
       UHS.STATE,
       UHS.STATE_DATE,
       UHS.CREATE_DATE,
       UHS.OPER_TYPE,
       UHS.COMMENTS,
       UO.ORG_PATH_CODE
  FROM IOPR1.UOS_HOLIDAY_SYSTEM UHS,IOPR1.UOS_ORG UO 
 WHERE UHS.STATE = '10A'  and  UHS.ORG_ID(+) = UO.ORG_ID
 START WITH UO.ORG_ID = 1899
CONNECT BY UO.ORG_ID = PRIOR UO.PARENT_ID
 ORDER BY UO.ORG_PATH_CODE DESC;

Execution Plan
----------------------------------------------------------
Plan hash value: 1668392902

-------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                    |     1 |    91 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY                   |                    |     1 |    91 |     5  (20)| 00:00:01 |
|*  2 |   FILTER                         |                    |       |       |            |          |
|*  3 |    CONNECT BY WITH FILTERING     |                    |       |       |            |          |
|   4 |     NESTED LOOPS OUTER           |                    |     1 |   128 |     4   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID | UOS_ORG            |     1 |    74 |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN          | PK_UOS_ORG         |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL           | UOS_HOLIDAY_SYSTEM |     1 |    54 |     3   (0)| 00:00:01 |
|   8 |     NESTED LOOPS OUTER           |                    |     1 |    91 |     4   (0)| 00:00:01 |
|   9 |      NESTED LOOPS                |                    |       |       |            |          |
|  10 |       CONNECT BY PUMP            |                    |       |       |            |          |
|  11 |       TABLE ACCESS BY INDEX ROWID| UOS_ORG            |     1 |    37 |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN         | PK_UOS_ORG         |     1 |       |     1   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS FULL           | UOS_HOLIDAY_SYSTEM |     1 |    54 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter("UHS"."STATE"='10A')
   3 - access("UO"."ORG_ID"=PRIOR "UO"."PARENT_ID")
   6 - access("UO"."ORG_ID"=1899)
   7 - filter("UHS"."ORG_ID"(+)="UO"."ORG_ID")
  12 - access("UO"."ORG_ID"=PRIOR "UO"."PARENT_ID")
  13 - filter("UHS"."ORG_ID"(+)="UO"."ORG_ID")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
       1129  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
两者区别：
当前的执行计划是2个表先外连接得到一个较大行源，再根据UO.ORG_ID = 1899筛选数据；
而建议改后的执行计划是首先根据UO.ORG_ID = 1899筛选数据得到一个较小行源，再去跟 UOS_HOLIDAY_SYSTEM做外连接。 最终整体的SQL语句逻辑读由2720降低到32。