NVL 对sql性能的影响


    SELECT BOOKING.JOBNO AS JOBNO, /*JOB_NO*/
           BOOKING.OBLNO AS OBLNO, /*主单号*/
           BOOKING.HBLNO AS HBLNO, /*分单号*/
           PCS, /*件数*/
           GWT, /*计费重*/
           CBM, /*体积*/
           RECEIPT_DATE, /*到货日期，出口=ATD,进口=ATA*/
           BOOKING.BOOKING_ID, /*分单主键*/
           FREIGHT_R.FR_SETTLEMENT_NAME AS SETTLEMENT_NAME, /*客户*/
           BOOKING.FR_ACCOUNT_STATUS AS ACCOUNT_STATUS, /*是否上传*/
           FI.FS_INV_NO AS FS_INV_NO, /*发票号*/
           /*发票总额,暂时不考虑部分开发票情况;未开发票=费用金额，全部开发票=发票费用明细的发票金额*/
           ROUND(SUM(NVL(DECODE(FREIGHT_R.Fr_Invoice_Status,0,FREIGHT_R.FR_AMOUNT,FIFD.FS_INVOICE_AMOUNT),
                   0) * NVL(FREIGHT_R.FR_LOCAL_CURR_RATE, 1)),2) AS INVOICE_AMOUNT,
           H_PAYMENT, /*分单成本*/
           PAYMENT, /*主单成本*/
           F_R_LOCK, /*是否应收已挂账*/
           F_P_LOCK, /*是否应付已挂账*/
           'Z' AS OFFICETYPE /*公司类型--根据参数传递替换，Z=子公司,F=分公司*/
      FROM ( /*1.取各个分单的业务数据，以及分单成本和主单成本
               分单成本 = 该分单下所有应付费用折换为RMB的总和
               主单成本 = 该分单对应的主单下的所有应付费用折换为RMB的总和 */
            SELECT BOOKING.IOB_BUSINESS_NO AS JOBNO, /*JOB_NO*/
                    --format_mawbno(BOOKING.IOB_MAWB_NO) AS OBLNO, /*主单号*/
                    BOOKING.IOB_MAWB_NO AS OBLNO,
                    BOOKING.IOB_HAWB_NO AS HBLNO, /*分单号*/
                    CARGO.BK_CRG_COST_PCS AS PCS, /*件数*/
                    CARGO.BK_CRG_COST_FEE_GWT AS GWT, /*计费重*/
                    CARGO.BK_CRG_COST_CBM AS CBM, /*体积*/
                    DECODE(BOOKING.IOB_IO_TYPE,'O',BOOKING.IOB_ATD,BOOKING.IOB_ATA) AS RECEIPT_DATE, /*到货日期，出口=ATD,进口=ATA*/
                    ROUND(SUM(NVL(FREIGHT_H_P.FR_AMOUNT,0)*NVL(FREIGHT_H_P.FR_LOCAL_CURR_RATE,1)),2) AS H_PAYMENT, /*分单成本金额*/
                    PAYMENT, /*主单成本金额*/
                    decode(BOOKING.IOB_FRT_R_LOCK_STATUS, 'T', 'Y', 'N') AS F_R_LOCK, /*是否应收锁定*/
                    decode(BOOKING.IOB_FRT_P_LOCK_STATUS, 'T', 'Y', 'N') AS F_P_LOCK, /*是否应付锁定*/
                    BOOKING.IOB_BOOKING_ID AS BOOKING_ID, /*分单的主键*/
                    BOOKING.IOB_MAWB_BOOKING_ID AS MAWB_BOOKING_ID, /*主单的主键*/
                    '已上传' AS FR_ACCOUNT_STATUS
              FROM KFMSV2_AIR.VW_FS_BOOKING BOOKING
              LEFT JOIN KFMSV2_AIR.BK_CARGO CARGO ON BOOKING.IOB_BOOKING_ID =CARGO.BK_CRG_BOOKING_ID
                                      AND BOOKING.IOB_IO_TYPE =CARGO.BK_CRG_IO_TYPE
              /*计算分单成本*/
              LEFT JOIN KFMSV2_AIR.FS_FREIGHT FREIGHT_H_P ON BOOKING.IOB_BOOKING_ID = FREIGHT_H_P.FR_BOOKING_ID
                                              AND FREIGHT_H_P.FR_RP_FLAG = 'P' AND FREIGHT_H_P.FR_RB_FLAG >= 'B' AND FREIGHT_H_P.FR_RB_FLAG <= 'C'
              /*计算主单成本*/
              LEFT JOIN (SELECT ROUND(SUM(NVL(FREIGHT_M_P.FR_AMOUNT,0)*NVL(FREIGHT_M_P.FR_LOCAL_CURR_RATE,1)),2) AS PAYMENT,
                                NVL(BOOKING_M.IOB_MAWB_BOOKING_ID,BOOKING_M.IOB_BOOKING_ID) AS IOB_MAWB_BOOKING_ID
                           FROM KFMSV2_AIR.VW_FS_BOOKING BOOKING_M,KFMSV2_AIR.FS_FREIGHT FREIGHT_M_P
                          WHERE 1 = 1
                            AND BOOKING_M.IOB_BOOKING_ID = FREIGHT_M_P.FR_BOOKING_ID
                            AND BOOKING_M.IOB_BOOKING_ID IS NOT NULL
                            -- AND BOOKING.IOB_MAWB_BOOKING_ID = BOOKING_M.IOB_MAWB_BOOKING_ID
                            --AND BOOKING_M.IOB_MHWB_FLAG = 1
                            AND FREIGHT_M_P.FR_RP_FLAG = 'P' AND FREIGHT_M_P.FR_RB_FLAG >= 'B' AND FREIGHT_M_P.FR_RB_FLAG <= 'C'
                          GROUP BY NVL(BOOKING_M.IOB_MAWB_BOOKING_ID,BOOKING_M.IOB_BOOKING_ID)) BOOKING_M ON NVL(BOOKING.IOB_MAWB_BOOKING_ID,BOOKING.IOB_BOOKING_ID) = BOOKING_M.IOB_MAWB_BOOKING_ID
             WHERE 1 = 1 AND BOOKING.IOB_IS_CANCELLED = 0 AND (BOOKING.IOB_MHWB_FLAG = 1 OR BOOKING.IOB_MHWB_FLAG = 5)  AND BOOKING.IOB_ORGID='SHA' AND BOOKING.IOB_IO_TYPE='O' AND BOOKING.IOB_ETD>=to_date('2010-09-01'||' 00:00:00','yyyy-mm-dd hh24:mi:ss') AND BOOKING.IOB_ETD<=to_date('2010-10-01'||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
             GROUP BY BOOKING.IOB_BUSINESS_NO, /*JOB_NO*/
                       BOOKING.IOB_MAWB_NO, /*主单号*/
                       BOOKING.IOB_HAWB_NO, /*分单号*/
                       CARGO.BK_CRG_COST_PCS, /*件数*/
                       CARGO.BK_CRG_COST_FEE_GWT, /*计费重*/
                       CARGO.BK_CRG_COST_CBM, /*体积*/
                       DECODE(BOOKING.IOB_IO_TYPE,'O',BOOKING.IOB_ATD,BOOKING.IOB_ATA),/*到货日期，出口=ATD,进口=ATA*/
                       BOOKING.IOB_FRT_R_LOCK_STATUS, /*是否应收锁定*/
                       BOOKING.IOB_FRT_P_LOCK_STATUS, /*是否应付锁定*/
                       BOOKING.IOB_BOOKING_ID, /*分单的主键*/
                       PAYMENT,
                       BOOKING.IOB_MAWB_BOOKING_ID) BOOKING,FS_FREIGHT FREIGHT_R
      LEFT JOIN KFMSV2_AIR.FS_INV_FRT_DETAIL FIFD ON FREIGHT_R.FR_FREIGHT_ID = FIFD.FS_IFD_FRT_ID
      LEFT JOIN KFMSV2_AIR.FS_INVOICE FI ON FIFD.FS_INV_ID = FI.FS_INV_ID
     WHERE BOOKING.BOOKING_ID = FREIGHT_R.FR_BOOKING_ID
       AND FREIGHT_R.FR_RP_FLAG = 'R' --条件1
        /*AND  NVL(FREIGHT_R.FR_RP_FLAG,'R') = 'R'*/--条件2
        AND NVL(FREIGHT_R.FR_SUB_FLAG,'F')='T'
     GROUP BY BOOKING.JOBNO, /*JOB_NO*/
              BOOKING.OBLNO, /*主单号*/
              BOOKING.HBLNO, /*分单号*/
              PCS, /*件数*/
              GWT, /*计费重*/
              CBM, /*体积*/
              RECEIPT_DATE, /*到货日期，出口=ATD,进口=ATA*/
              BOOKING.BOOKING_ID, /*分单主键*/
              BOOKING.MAWB_BOOKING_ID,
              FREIGHT_R.FR_SETTLEMENT_NAME, /*客户*/
              BOOKING.FR_ACCOUNT_STATUS, /*是否上传*/
              FI.FS_INV_NO, /*发票号*/
              H_PAYMENT, /*分单成本*/
              PAYMENT, /*主单成本*/
              F_R_LOCK, /*是否应收已挂账*/
              F_P_LOCK /*是否应付已挂账*/
     ORDER BY BOOKING.MAWB_BOOKING_ID,
              BOOKING.OBLNO,
              BOOKING.JOBNO,
              BOOKING.HBLNO,
              FREIGHT_R.FR_SETTLEMENT_NAME /*一定要按此排序*/
问题:以上SQL 条件1 改成 条件2   整个SQL的性能提升了10多倍，哪位可以介绍下

解决方案 »

  1.   

    这个应该跟NVL函数本身的执行效率没什么关系，这两个的执行计划应该不一样而已。在[FREIGHT_R.FR_RP_FLAG]这个字段有索引的情况下，他们的执行计划可能不一样的，你把这两个SQL执行计划贴上来，看看是不是别的问题。
      

  2.   

    你说条件 where nvl(col,'R')='R' 和直接 where col='R' 有上面区别？？？
    对空值的处理不一样！
      

  3.   

    条件1的执行计划
    SELECT STATEMENT, GOAL = ALL_ROWS Cost=78588578 Cardinality=512105952 Bytes=116760157056
     SORT GROUP BY Cost=78588578 Cardinality=512105952 Bytes=116760157056
      VIEW Object owner=SYS Cost=44278721 Cardinality=512105952 Bytes=116760157056
       HASH GROUP BY Cost=44278721 Cardinality=512105952 Bytes=152095467744
        HASH JOIN RIGHT OUTER Cost=11655 Cardinality=512105952 Bytes=152095467744
         TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=313 Cardinality=20111 Bytes=341887
         HASH JOIN OUTER Cost=2541 Cardinality=25209716 Bytes=7058720480
          HASH JOIN RIGHT OUTER Cost=645 Cardinality=7390 Bytes=1877060
           TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_INVOICE Cost=55 Cardinality=3412 Bytes=85300
           HASH JOIN RIGHT OUTER Cost=589 Cardinality=7390 Bytes=1692310
            TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_INV_FRT_DETAIL Cost=42 Cardinality=5821 Bytes=151346
            HASH JOIN Cost=546 Cardinality=7390 Bytes=1500170
             HASH JOIN OUTER Cost=230 Cardinality=1053 Bytes=154791
              VIEW Object owner=AIR_UAT Object name=VW_FS_BOOKING Cost=172 Cardinality=1053 Bytes=138996
               UNION-ALL
                TABLE ACCESS FULL Object owner=AIR_UAT Object name=OB_BOOKING Cost=169 Cardinality=1049 Bytes=58744
                FILTER
                 TABLE ACCESS FULL Object owner=AIR_UAT Object name=IB_BOOKING Cost=160 Cardinality=3950 Bytes=225150
                FILTER
                 TABLE ACCESS FULL Object owner=AIR_UAT Object name=NB_BOOKING Cost=3 Cardinality=1 Bytes=38
                FILTER
                 TABLE ACCESS FULL Object owner=AIR_UAT Object name=BOND_BOOKING Cost=3 Cardinality=1 Bytes=25
                FILTER
                 TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=1 Bytes=53
              TABLE ACCESS FULL Object owner=AIR_UAT Object name=BK_CARGO Cost=57 Cardinality=7874 Bytes=118110
             TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=316 Cardinality=6947 Bytes=389032
          VIEW Object owner=AIR_UAT Cost=749 Cardinality=341151 Bytes=8869926
           HASH GROUP BY Cost=749 Cardinality=341151 Bytes=14669493
            HASH JOIN Cost=700 Cardinality=341151 Bytes=14669493
             TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=313 Cardinality=20111 Bytes=341887
             VIEW Object owner=AIR_UAT Object name=VW_FS_BOOKING Cost=379 Cardinality=16794 Bytes=436644
              UNION-ALL
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=OB_BOOKING Cost=171 Cardinality=6356 Bytes=57204
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=IB_BOOKING Cost=162 Cardinality=8305 Bytes=91355
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=NB_BOOKING Cost=3 Cardinality=19 Bytes=133
               INDEX FULL SCAN Object owner=AIR_UAT Object name=PK_BOND_BOOKING Cost=1 Cardinality=1 Bytes=4
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=2113 Bytes=14791
      

  4.   

    条件2 的执行计划
    SELECT STATEMENT, GOAL = ALL_ROWS Cost=8839958 Cardinality=93841187 Bytes=21583473010
     SORT GROUP BY Cost=8839958 Cardinality=93841187 Bytes=21583473010
      HASH JOIN Cost=2506586 Cardinality=93841187 Bytes=21583473010
       HASH JOIN RIGHT OUTER Cost=415 Cardinality=6947 Bytes=493237
        TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_INVOICE Cost=55 Cardinality=3412 Bytes=44356
        HASH JOIN RIGHT OUTER Cost=359 Cardinality=6947 Bytes=402926
         TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_INV_FRT_DETAIL Cost=42 Cardinality=5821 Bytes=81494
         TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=316 Cardinality=6947 Bytes=305668
       VIEW Object owner=AIR_UAT Cost=2504558 Cardinality=13372129 Bytes=2126168511
        HASH GROUP BY Cost=2504558 Cardinality=13372129 Bytes=2540704510
         HASH JOIN RIGHT OUTER Cost=2610 Cardinality=72973788 Bytes=13865019720
          TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=313 Cardinality=20111 Bytes=341887
          HASH JOIN OUTER Cost=1041 Cardinality=3592320 Bytes=621471360
           HASH JOIN OUTER Cost=230 Cardinality=1053 Bytes=154791
            VIEW Object owner=AIR_UAT Object name=VW_FS_BOOKING Cost=172 Cardinality=1053 Bytes=138996
             UNION-ALL
              TABLE ACCESS FULL Object owner=AIR_UAT Object name=OB_BOOKING Cost=169 Cardinality=1049 Bytes=58744
              FILTER
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=IB_BOOKING Cost=160 Cardinality=3950 Bytes=225150
              FILTER
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=NB_BOOKING Cost=3 Cardinality=1 Bytes=38
              FILTER
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=BOND_BOOKING Cost=3 Cardinality=1 Bytes=25
              FILTER
               TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=1 Bytes=53
            TABLE ACCESS FULL Object owner=AIR_UAT Object name=BK_CARGO Cost=57 Cardinality=7874 Bytes=118110
           VIEW Object owner=AIR_UAT Cost=749 Cardinality=341151 Bytes=8869926
            HASH GROUP BY Cost=749 Cardinality=341151 Bytes=14669493
             HASH JOIN Cost=700 Cardinality=341151 Bytes=14669493
              TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=313 Cardinality=20111 Bytes=341887
              VIEW Object owner=AIR_UAT Object name=VW_FS_BOOKING Cost=379 Cardinality=16794 Bytes=436644
               UNION-ALL
                TABLE ACCESS FULL Object owner=AIR_UAT Object name=OB_BOOKING Cost=171 Cardinality=6356 Bytes=57204
                TABLE ACCESS FULL Object owner=AIR_UAT Object name=IB_BOOKING Cost=162 Cardinality=8305 Bytes=91355
                TABLE ACCESS FULL Object owner=AIR_UAT Object name=NB_BOOKING Cost=3 Cardinality=19 Bytes=133
                INDEX FULL SCAN Object owner=AIR_UAT Object name=PK_BOND_BOOKING Cost=1 Cardinality=1 Bytes=4
                TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=2113 Bytes=14791
      

  5.   

    这样吧，你把下面的语句执行完了后结果贴上来吧。
    注意缩进。也为我们找想这么长的SQL语句，怎么看。
    还有，你这两个SQL语句结果可能不一样的，所以不能这么改。
    SET AUTOTRACE TRACEONLY
    SET TIMING ON
      

  6.   

    1:
      TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=1 Bytes=53
      TABLE ACCESS FULL Object owner=AIR_UAT Object name=BK_CARGO Cost=57 Cardinality=7874 Bytes=118110
      TABLE ACCESS FULL Object owner=AIR_UAT Object name=FS_FREIGHT Cost=316 Cardinality=6947 Bytes=389032
      VIEW Object owner=AIR_UAT Cost=749 Cardinality=341151 Bytes=8869926
      HASH GROUP BY Cost=749 Cardinality=341151 Bytes=14669493
      HASH JOIN Cost=700 Cardinality=341151 Bytes=146694932:
      TABLE ACCESS FULL Object owner=AIR_UAT Object name=CUSTOM_ORDER Cost=42 Cardinality=1 Bytes=53
      TABLE ACCESS FULL Object owner=AIR_UAT Object name=BK_CARGO Cost=57 Cardinality=7874 Bytes=118110
      VIEW Object owner=AIR_UAT Cost=749 Cardinality=341151 Bytes=8869926
      HASH GROUP BY Cost=749 Cardinality=341151 Bytes=14669493
      HASH JOIN Cost=700 Cardinality=341151 Bytes=146694931多了一次全表扫描