Oracle_Pl、Sql_Nested loop

描述：此sql存在多个执行计划。项目组要根据他们提供加 hint的sql，从执行计划中选择一个和此加hint的sql进行匹配。在选择leading时，和项目组出现了分歧。根据测试leading中应选择根据where条件筛选之后结果集较少的作为leading（驱动表）。

1.SQL

select /*+ index(T1 XPK_TRAN_HIST_TOPIC)*/
SUM(T1.LOCAL_AMOUNT) AS TOTALLOCALAMOUNT,
SUM(T1.USD_AMOUNT) AS TOTALUSDAMOUNT
FROM TRAN_HIST_TOPIC T1, MDL_CHECK_RESULT T2
WHERE T2.TRS_SEQNO = T1.SEQNO
AND T2.CASE_ID = :1
AND t1.SOC_NO = to_char(:2)

SQL>select count(*) from MDL_CHECK_RESULT where case_id='DK2018050300000003620000000002'; T2

COUNT(*)
----------
3

SQL>select count(*) from TRAN_HIST_TOPIC where SOC_NO='123'; T1

COUNT(*)

----------
229194

2.

SQL>explain plan for select /*+ index(T1 XPK_TRAN_HIST_TOPIC) use_nl(T1,T2) leading(T1) */
  2  SUM(T1.LOCAL_AMOUNT) AS TOTALLOCALAMOUNT,
  3  SUM(T1.USD_AMOUNT) AS TOTALUSDAMOUNT             FROM TRAN_HIST_TOPIC
  4  T1 , MDL_CHECK_RESULT T2          WHERE T2.TRS_SEQNO = T1.SEQNO
  5  AND T2.CASE_ID = 'DK2018050300000003620000000002'     AND t1.SOC_NO = to_char(123);
SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3321045997

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |     1 |   158 |   575K  (1)| 01:55:08 |       |       |
|   1 |  SORT AGGREGATE                      |                      |     1 |   158 |            |          |       |       |
|   2 |   NESTED LOOPS                       |                      |    57 |  9006 |   575K  (1)| 01:55:08 |       |       |
|   3 |    PARTITION LIST SINGLE             |                      |   229K|    12M|   116K  (1)| 00:23:16 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TRAN_HIST_TOPIC      |   229K|    12M|   116K  (1)| 00:23:16 |    40 |    40 |
|*  5 |      INDEX RANGE SCAN                | XPK_TRAN_HIST_TOPIC  |   437 |       |  2318   (1)| 00:00:28 |    40 |    40 |
|*  6 |    INDEX UNIQUE SCAN                 | XPK_MDL_CHECK_RESULT |     1 |   102 |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------

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

   5 - access("T1"."SOC_NO"='123')
   6 - access("T2"."CASE_ID"=U'DK2018050300000003620000000002' AND "T2"."TRS_SEQNO"="T1"."SEQNO")

3.

SQL>explain plan for select /*+ index(T1 XPK_TRAN_HIST_TOPIC) use_nl(T1,T2) leading(T2) */
  2  SUM(T1.LOCAL_AMOUNT) AS TOTALLOCALAMOUNT,
  3  SUM(T1.USD_AMOUNT) AS TOTALUSDAMOUNT             FROM TRAN_HIST_TOPIC
T1 , MDL_CHECK_RESULT T2          WHERE T2.TRS_SEQNO = T1.SEQNO
  4    5  AND T2.CASE_ID = 'DK2018050300000003620000000002'     AND t1.SOC_NO = to_char(123);
SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3118427468

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   158 |   119   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                     |                      |     1 |   158 |            |          |       |       |
|   2 |   NESTED LOOPS                      |                      |    57 |  9006 |   119   (0)| 00:00:02 |       |       |
|   3 |    NESTED LOOPS                     |                      |    57 |  9006 |   119   (0)| 00:00:02 |       |       |
|*  4 |     INDEX RANGE SCAN                | XPK_MDL_CHECK_RESULT |    57 |  5814 |     5   (0)| 00:00:01 |       |       |
|   5 |     PARTITION LIST SINGLE           |                      |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |      INDEX UNIQUE SCAN              | XPK_TRAN_HIST_TOPIC  |     1 |       |     1   (0)| 00:00:01 |    40 |    40 |
|   7 |    TABLE ACCESS BY LOCAL INDEX ROWID| TRAN_HIST_TOPIC      |     1 |    56 |     2   (0)| 00:00:01 |    40 |    40 |
----------------------------------------------------------------------------------------------------------------------------

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

   4 - access("T2"."CASE_ID"=U'DK2018050300000003620000000002')
   6 - access("T1"."SOC_NO"='123' AND "T2"."TRS_SEQNO"="T1"."SEQNO")

 

参考：https://pan.baidu.com/s/1EeDnayQuQA9JN8yAV8n60g  lxxa