index_ffs, leading,merge,no_merge,no_unnest,use_hash

最新推荐文章于 2023-07-03 11:18:23 发布

原创最新推荐文章于 2023-07-03 11:18:23 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种具体的SQL查询优化方案，包括使用特定索引、驱动表的选择、hash join的应用及查询合并技巧等，旨在提高数据库查询效率。

--------------------------------------------------------------------index_ffs----------------------------------------

/*+ index_ffs(Peia PA_EXPENDITURE_ITEMS_N30) index_ffs(Mtln MTL_TRANSACTION_LOT_NUMBERS_N6) index_ffs(Rlt RCV_LOT_TRANSACTIONS_N3) index(Mmt MTL_MATERIAL_TRANSACTIONS_N5) use_hash(mmt,mtln)*/

--------------------------------------------------------------------leading------------------------------------------

我想 mmt 驱动 mtln
写 mmt吗
use_hash(mmt mtln) LEADING(mmt)

SP做驱动表 ,SP 与XJ 做hash join

WITH KH AS (SELECT DISTINCT T.ZUOY_STAFF, T.DANJ_NO, T.HANGHAO
                FROM Viw_Yw_Zyryjl_Bak T
               where T.CAOZJS_ID IN ('KHJS0000182', 'KHJS0000013')) ,
      SP AS (SELECT SHANGP_ID , BAOZ_NUM FROM JC_SPZL A
               WHERE SUBSTR(A.SHANGP_NO, 1, 1) <> 'P'
                 AND SUBSTR(A.SHANGP_NO, 1, 1) <> 'Z')
SELECT /*+   leading(SP XJ ) */ kh.ZUOY_STAFF 拣货员,
        COUNT(DISTINCT XJ.DANJ_NO) 单据数,
        SUM(XJ.SHIJ_NUM) ZSL,
        COUNT(xj.SHANGP_ID) TMS,
        COUNT(DISTINCT XJ.SHANGP_ID) PGS,
        ROUND(SUM(XJ.SHIJ_NUM / SP.BAOZ_NUM), 2) JS
   FROM SP, Viw_Yw_Xjzl_Bak XJ ,KH
WHERE XJ.SHANGP_ID = SP.SHANGP_ID
    and xj.DANJ_NO = KH.DANJ_NO
    AND XJ.HANGHAO = KH.HANGHAO
    AND XJ.HUOW_ID <> '冲'
    AND XJ.SHIJ_NUM > 0
    AND XJ.YEW_TYPE IN ('2', '3')
    AND XJ.KUB = 'LHK'
    AND XJ.SHENGCHEN_TIME >= to_timestamp(TRUNC(TO_DATE('2016-06-01', 'yyyy-mm-dd')))
    AND XJ.SHENGCHEN_TIME < to_timestamp(TRUNC(TO_DATE('2016-06-23', 'yyyy-mm-dd') + 1))
group by kh.ZUOY_STAFF

-----------------------------------------------------------merge & no_merge-----------------------------------------------

SELECT /*+no_merge(XJ) */
              (SELECT DISTINCT T.ZUOY_STAFF FROM VIW_YW_ZYRYJL T WHERE T.DANJ_NO=XJ.DANJ_NO AND T.HANGHAO=XJ.HANGHAO AND T.CAOZJS_ID IN(
               SELECT A.JIAOS_ID FROM KH_JC_JSZD A WHERE A.JIAOS_NAME IN('拆零拣货员','整件拣货员'))) ZUOY_STAFF,
               XJ.DANJ_NO,
               SP.SHANGP_NO,
               SUM(XJ.SHIJ_NUM) ZSL,
               COUNT(DISTINCT XJ.DANJ_NO||XJ.HANGHAO) TMS,
               COUNT(DISTINCT XJ.SHANGP_ID) PGS,
               ROUND(SUM(XJ.SHIJ_NUM / SP.BAOZ_NUM),2) JS
          FROM JC_SPZL SP,VIW_CK_KPD_HZ ZY, VIW_YW_XJZL XJ
         WHERE ZY.DANJ_NO = XJ.DANJ_NO
           AND SP.SHANGP_ID = xj.SHANGP_ID
           AND XJ.YEW_TYPE IN ('2', '3')
           AND XJ.KUB = 'LHK'
           AND XJ.HUOW_ID <> '冲'
       --    and TRUNC(cast(SHENGCHEN_TIME AS DATE)) >= TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd'))
        --   AND TRUNC(cast(SHENGCHEN_TIME AS DATE)) < TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
          AND cast(XJ.SHENGCHEN_TIME AS DATE) >= TRUNC(TO_DATE('2016-06-01' ,'yyyy-mm-dd'))
           AND cast(XJ.SHENGCHEN_TIME AS DATE) < TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
           AND XJ.SHIJ_NUM > 0
           AND SUBSTR(SP.SHANGP_NO,1,1) <>'P'
           AND SUBSTR(SP.SHANGP_NO,1,1)<>'Z'
         GROUP BY
                  XJ.DANJ_NO,
                  XJ.PINGX_NO,
                  XJ.HANGHAO,
                  SP.SHANGP_NO,
                  XJ.LOT

-------------------------------merge---------------------------------------------------------------------

19. /*+ MERGE(TABLE) */
能够对视图的各个查询进行相应的合并.
例如:
SELECT /*+ MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/

SELECT /*+no_merge(XJ) */
              (SELECT DISTINCT T.ZUOY_STAFF FROM VIW_YW_ZYRYJL T WHERE T.DANJ_NO=XJ.DANJ_NO AND T.HANGHAO=XJ.HANGHAO AND T.CAOZJS_ID IN(
               SELECT A.JIAOS_ID FROM KH_JC_JSZD A WHERE A.JIAOS_NAME IN('拆零拣货员','整件拣货员'))) ZUOY_STAFF,
               XJ.DANJ_NO,
               SP.SHANGP_NO,
               SUM(XJ.SHIJ_NUM) ZSL,
               COUNT(DISTINCT XJ.DANJ_NO||XJ.HANGHAO) TMS,
               COUNT(DISTINCT XJ.SHANGP_ID) PGS,
               ROUND(SUM(XJ.SHIJ_NUM / SP.BAOZ_NUM),2) JS
          FROM JC_SPZL SP,VIW_CK_KPD_HZ ZY, VIW_YW_XJZL XJ
         WHERE ZY.DANJ_NO = XJ.DANJ_NO
           AND SP.SHANGP_ID = xj.SHANGP_ID
           AND XJ.YEW_TYPE IN ('2', '3')
           AND XJ.KUB = 'LHK'
           AND XJ.HUOW_ID <> '冲'
       --    and TRUNC(cast(SHENGCHEN_TIME AS DATE)) >= TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd'))
        --   AND TRUNC(cast(SHENGCHEN_TIME AS DATE)) < TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
          AND cast(XJ.SHENGCHEN_TIME AS DATE) >= TRUNC(TO_DATE('2016-06-01' ,'yyyy-mm-dd'))
           AND cast(XJ.SHENGCHEN_TIME AS DATE) < TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
           AND XJ.SHIJ_NUM > 0
           AND SUBSTR(SP.SHANGP_NO,1,1) <>'P'
           AND SUBSTR(SP.SHANGP_NO,1,1)<>'Z'
         GROUP BY
                  XJ.DANJ_NO,
                  XJ.PINGX_NO,
                  XJ.HANGHAO,
                  SP.SHANGP_NO,
                  XJ.LOT

-------------------no_unnest------------------------------

由hash改成nest_loop

执行计划
----------------------------------------------------------
Plan hash value: 1382173668

--------------------------------------------------------------------------------
-------------------------

| Id | Operation                       | Name                  | Rows | Bytes
| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------------

|   0 | SELECT STATEMENT                |                       |     3 |   621
|   641   (0)| 00:00:08 |

|   1 | TABLE ACCESS BY INDEX ROWID    | JC_DWZL               |     1 |    18
|     2   (0)| 00:00:01 |

|* 2 |   INDEX UNIQUE SCAN             | PK_JC_DWZL            |     1 |
|     1   (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID    | JC_ZDWH_MX            |     1 |    24
|     3   (0)| 00:00:01 |

|* 4 |   INDEX RANGE SCAN              | PK_JC_ZDWH_MX         |     3 |
|     2   (0)| 00:00:01 |

|   5 | TABLE ACCESS BY INDEX ROWID    | JC_DWZL               |     1 |    38
|     2   (0)| 00:00:01 |

|* 6 |   INDEX UNIQUE SCAN             | PK_JC_DWZL            |     1 |
|     1   (0)| 00:00:01 |

|   7 | TABLE ACCESS BY INDEX ROWID    | JC_DWZL               |     1 |    38
|     2   (0)| 00:00:01 |

|* 8 |   INDEX UNIQUE SCAN             | PK_JC_DWZL            |     1 |
|     1   (0)| 00:00:01 |

|   9 | TABLE ACCESS BY INDEX ROWID    | PS_ZCD_MX             |     1 |    30
|     4   (0)| 00:00:01 |

|* 10 |   INDEX RANGE SCAN              | IDX_PS_ZCD_MX_DANJ_NO |     1 |
|     3   (0)| 00:00:01 |

| 11 | SORT AGGREGATE                 |                       |     1 |    44
|            |          |

| 12 |   NESTED LOOPS                  |                       |       |
|            |          |

| 13 |    NESTED LOOPS                 |                       |     5 |   220
|     9   (0)| 00:00:01 |

| 14 |     TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   140
|     4   (0)| 00:00:01 |

|* 15 |      INDEX RANGE SCAN           | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 16 |     INDEX UNIQUE SCAN           | PK_JC_SPZL            |     1 |
|     0   (0)| 00:00:01 |

| 17 |    TABLE ACCESS BY INDEX ROWID | JC_SPZL               |     1 |    16
|     1   (0)| 00:00:01 |

| 18 | SORT AGGREGATE                 |                       |     1 |    40
|            |          |

| 19 |   NESTED LOOPS                  |                       |     5 |   200
|     4   (0)| 00:00:01 |

| 20 |    TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   140
|     4   (0)| 00:00:01 |

|* 21 |     INDEX RANGE SCAN            | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 22 |    INDEX UNIQUE SCAN            | PK_JC_SPZL            |     1 |    12
|     0   (0)| 00:00:01 |

| 23 | SORT AGGREGATE                 |                       |     1 |    40
|            |          |

| 24 |   NESTED LOOPS                  |                       |     5 |   200
|     4   (0)| 00:00:01 |

| 25 |    TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   140
|     4   (0)| 00:00:01 |

|* 26 |     INDEX RANGE SCAN            | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 27 |    INDEX UNIQUE SCAN            | PK_JC_SPZL            |     1 |    12
|     0   (0)| 00:00:01 |

| 28 | SORT GROUP BY                  |                       |     1 |    25
|            |          |

| 29 |   TABLE ACCESS BY INDEX ROWID   | CK_KPD_MX             |     5 |   125
|     4   (0)| 00:00:01 |

|* 30 |    INDEX RANGE SCAN             | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

| 31 | SORT AGGREGATE                 |                       |     1 |    28
|            |          |

| 32 |   TABLE ACCESS BY INDEX ROWID   | CK_KPD_MX             |     5 |   140
|     4   (0)| 00:00:01 |

|* 33 |    INDEX RANGE SCAN             | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

| 34 | SORT AGGREGATE                 |                       |     1 |    43
|            |          |

| 35 |   NESTED LOOPS SEMI             |                       |     5 |   215
|     9   (0)| 00:00:01 |

| 36 |    TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 37 |     INDEX RANGE SCAN            | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 38 |    TABLE ACCESS BY INDEX ROWID | JC_SPZL               | 14757 |   230K
|     1   (0)| 00:00:01 |

|* 39 |     INDEX UNIQUE SCAN           | PK_JC_SPZL            |     1 |
|     0   (0)| 00:00:01 |

| 40 | SORT AGGREGATE                 |                       |     1 |    43
|            |          |

| 41 |   NESTED LOOPS SEMI             |                       |     5 |   215
|     9   (0)| 00:00:01 |

| 42 |    TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   140
|     4   (0)| 00:00:01 |

|* 43 |     INDEX RANGE SCAN            | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 44 |    TABLE ACCESS BY INDEX ROWID | JC_SPZL               |   295 | 4425
|     1   (0)| 00:00:01 |

|* 45 |     INDEX UNIQUE SCAN           | PK_JC_SPZL            |     1 |
|     0   (0)| 00:00:01 |

| 46 | SORT AGGREGATE                 |                       |     1 |     7
|            |          |

|* 47 |   INDEX RANGE SCAN              | PK_JC_ZJCKPHS         |     1 |     7
|     0   (0)| 00:00:01 |

| 48 | SORT AGGREGATE                 |                       |     1 |    17
|            |          |

| 49 |   TABLE ACCESS BY INDEX ROWID   | CK_KPD_MX             |     5 |    85
|     4   (0)| 00:00:01 |

|* 50 |    INDEX RANGE SCAN             | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

| 51 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 52 |   NESTED LOOPS                  |                       |       |
|            |          |

| 53 |    NESTED LOOPS                 |                       |     1 |    67
|    30   (0)| 00:00:01 |

| 54 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|* 55 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 56 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 57 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|* 58 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|* 59 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 60 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 61 |   NESTED LOOPS                  |                       |       |
|            |          |

| 62 |    NESTED LOOPS                 |                       |     1 |    67
|    30   (0)| 00:00:01 |

| 63 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|* 64 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 65 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 66 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|* 67 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|* 68 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 69 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 70 |   NESTED LOOPS                  |                       |       |
|            |          |

| 71 |    NESTED LOOPS                 |                       |     1 |    67
|    30   (0)| 00:00:01 |

| 72 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|* 73 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 74 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 75 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|* 76 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|* 77 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 78 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 79 |   NESTED LOOPS                  |                       |       |
|            |          |

| 80 |    NESTED LOOPS                 |                       |     2 |   134
|    30   (0)| 00:00:01 |

| 81 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|* 82 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 83 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 84 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|* 85 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|* 86 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 87 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 88 |   NESTED LOOPS                  |                       |       |
|            |          |

| 89 |    NESTED LOOPS                 |                       |     1 |    67
|    30   (0)| 00:00:01 |

| 90 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|* 91 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|* 92 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|* 93 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|* 94 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|* 95 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 96 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 97 |   NESTED LOOPS                  |                       |       |
|            |          |

| 98 |    NESTED LOOPS                 |                       |     2 |   134
|    30   (0)| 00:00:01 |

| 99 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|*100 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|*101 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|*102 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|*103 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|*104 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 105 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 106 |   NESTED LOOPS                  |                       |       |
|            |          |

| 107 |    NESTED LOOPS                 |                       |     1 |    67
|    30   (0)| 00:00:01 |

| 108 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|*109 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|*110 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|*111 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|*112 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|*113 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 114 | SORT GROUP BY                  |                       |     1 |    67
|            |          |

| 115 |   NESTED LOOPS                  |                       |       |
|            |          |

| 116 |    NESTED LOOPS                 |                       |     2 |   134
|    30   (0)| 00:00:01 |

| 117 |     NESTED LOOPS                |                       |    16 |   816
|    14   (0)| 00:00:01 |

|*118 |      TABLE ACCESS BY INDEX ROWID| CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|*119 |       INDEX RANGE SCAN          | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|*120 |      INDEX RANGE SCAN           | PK_KC_SPPHHW          |     3 |    72
|     2   (0)| 00:00:01 |

|*121 |     INDEX UNIQUE SCAN           | PK_JC_HWZD            |     1 |
|     0   (0)| 00:00:01 |

|*122 |    TABLE ACCESS BY INDEX ROWID | JC_HWZD               |     1 |    16
|     1   (0)| 00:00:01 |

| 123 | SORT AGGREGATE                 |                       |     1 |    48
|            |          |

| 124 |   NESTED LOOPS                  |                       |     5 |   240
|     9   (0)| 00:00:01 |

| 125 |    TABLE ACCESS BY INDEX ROWID | CK_KPD_MX             |     5 |   135
|     4   (0)| 00:00:01 |

|*126 |     INDEX RANGE SCAN            | PK_CK_KPD_MX          |     5 |
|     3   (0)| 00:00:01 |

|*127 |    INDEX UNIQUE SCAN            | PK_JC_PHWHB           |     1 |    21
|     1   (0)| 00:00:01 |

| 128 | NESTED LOOPS SEMI              |                       |     3 |   621
|   641   (0)| 00:00:08 |

| 129 |   NESTED LOOPS                  |                       |     3 |   570
|   632   (0)| 00:00:08 |

| 130 |    NESTED LOOPS ANTI            |                       |     3 |   432
|   629   (0)| 00:00:08 |

|*131 |     TABLE ACCESS BY INDEX ROWID | CK_KPD_HZ             |   306 | 39474
|    17   (0)| 00:00:01 |

|*132 |      INDEX RANGE SCAN           | IDX_CK_KPD_HZ_ZSSZC   |   306 |
|     3   (0)| 00:00:01 |

|*133 |     INDEX RANGE SCAN            | IDX_YW_XJZL_DJCH      | 80071 | 1172K
|     2   (0)| 00:00:01 |

| 134 |    TABLE ACCESS BY INDEX ROWID | JC_DWZL               |     1 |    46
|     1   (0)| 00:00:01 |

|*135 |     INDEX UNIQUE SCAN           | PK_JC_DWZL            |     1 |
|     0   (0)| 00:00:01 |

|*136 |   TABLE ACCESS BY INDEX ROWID   | CK_KPD_MX             |   162K| 2692K
|     3   (0)| 00:00:01 |

|*137 |    INDEX RANGE SCAN             | PK_CK_KPD_MX          |     1 |
|     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------------


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

   2 - access("DANW_ID"=:B1)
   3 - filter("ZHIDZ"=:B1)
   4 - access("ENGLISH_NAME"='TIH_WAY')
   6 - access("DANW_ID"=:B1)
   8 - access("DANW_ID"=:B1)
10 - access("DANJ_NO"=:B1)
15 - access("CK_KPD_MX"."DANJ_NO"=:B1)
16 - access("CK_KPD_MX"."SHANGP_ID"="JC_SPZL"."SHANGP_ID")
21 - access("CK_KPD_MX"."DANJ_NO"=:B1)
22 - access("CK_KPD_MX"."SHANGP_ID"="JC_SPZL"."SHANGP_ID")
26 - access("CK_KPD_MX"."DANJ_NO"=:B1)
27 - access("CK_KPD_MX"."SHANGP_ID"="JC_SPZL"."SHANGP_ID")
30 - access("CK_KPD_MX"."DANJ_NO"=:B1)
33 - access("CK_KPD_MX"."DANJ_NO"=:B1)
37 - access("CK_KPD_MX"."DANJ_NO"=:B1)
38 - filter("JIANG_FLG"<>'N' OR "FANGC_FLG"='Y')
39 - access("SHANGP_ID"="CK_KPD_MX"."SHANGP_ID")
43 - access("CK_KPD_MX"."DANJ_NO"=:B1)
44 - filter("YAOP_CATEGORY"='07')
45 - access("SHANGP_ID"="CK_KPD_MX"."SHANGP_ID")
47 - access("DANW_ID"=:B1)
50 - access("DANJ_NO"=:B1)
55 - filter("TA"."LINGS_NUM"<>0)
56 - access("TA"."DANJ_NO"=:B1)
57 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
58 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
59 - filter("TC"."QUYU_NO"='01')
64 - filter("TA"."LINGS_NUM"<>0)
65 - access("TA"."DANJ_NO"=:B1)
66 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
67 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
68 - filter("TC"."QUYU_NO"='02')
73 - filter("TA"."LINGS_NUM"<>0)
74 - access("TA"."DANJ_NO"=:B1)
75 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
76 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
77 - filter("TC"."QUYU_NO"='03')
82 - filter("TA"."LINGS_NUM"<>0)
83 - access("TA"."DANJ_NO"=:B1)
84 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
85 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
86 - filter("TC"."QUYU_NO"='04')
91 - filter("TA"."LINGS_NUM"<>0)
92 - access("TA"."DANJ_NO"=:B1)
93 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
94 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
95 - filter("TC"."QUYU_NO"='05')
100 - filter("TA"."LINGS_NUM"<>0)
101 - access("TA"."DANJ_NO"=:B1)
102 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
103 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
104 - filter("TC"."QUYU_NO"='06')
109 - filter("TA"."LINGS_NUM"<>0)
110 - access("TA"."DANJ_NO"=:B1)
111 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
112 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
113 - filter("TC"."QUYU_NO"='07')
118 - filter("TA"."LINGS_NUM"<>0)
119 - access("TA"."DANJ_NO"=:B1)
120 - access("TA"."SHANGP_ID"="TB"."SHANGP_ID")
121 - access("TB"."HUOW_ID"="TC"."HUOW_ID")
122 - filter("TC"."QUYU_NO"='08')
126 - access("C"."DANJ_NO"=:B1)
127 - access("C"."SHANGP_ID"="D"."SHANGP_ID" AND "C"."LOT_REQUEST"="D"."LOT")
131 - filter("XIAOS_TYPE"<>'5' AND "XIAOS_TYPE"<>'6')
132 - access("ZUOY_STATE"='D0' AND "SHIF_TBJ"='N' AND "SF_RGSQPH"='N' AND "ZHIX
_FLG"='Y' AND

              "CUOWCL_FLG"='N')
133 - access("DANJ_NO"="SYS_ALIAS_23"."DANJ_NO" AND "CHONGH_FLG"='N')
135 - access("SYS_ALIAS_23"."DANW_ID"="B"."DANW_ID")
136 - filter("NUM">0)
137 - access("DANJ_NO"="SYS_ALIAS_23"."DANJ_NO")

统计信息
----------------------------------------------------------
      20781 recursive calls
          0 db block gets
    1043432 consistent gets
          0 physical reads
         72 redo size
      86095 bytes sent via SQL*Net to client
        553 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
       5661 sorts (memory)
          0 sorts (disk)
        629 rows processed

TREE@TREE> spool off

AND NOT EXISTS (SELECT 1 FROM YW_XJZL WHERE DANJ_NO = A.DANJ_NO AND CHONGH_FLG = 'N' ) 由hash改成nest_loop

SELECT /*+ no_unnest(@SEL$28) */    --DECODE(B.YUHAI_FLG,'Y','裕海-'||B.DANW_NAME,PKG_TMS_PUB.FGET_FXNAME(B.LUX_NO)) PSFX,
2              DECODE(B.YUHAI_FLG,'Y','裕海-'||B.DANW_NAME,PKG_TMS_PUB.FGET_FXNAME(NVL((SELECT LUX_NO FROM JC_DWZL WHERE DANW_ID=A.ERJI_DANW_ID),B.LUX_NO))) PSFX,
3              B.YUHAI_FLG,

---------------------------------------------------use_hash-------------------------------

走hash
use_hash （t1,t2）