关于oracle视图查询速度的问题

看看视图里的SQL语句可否优化....

如果可以的话把你的sql语句贴出来看看
执行速度慢可能是很多方面的问题引起的

视图中主要就是有一个汇总的字段ysbalace 这个字段是现实余额，如果根据这个字段 ysbalance>0查会慢，根据其他的不慢，像这个字段也没法加索引啊！

还是贴出来吧，本人不怎么愿意用联接查询，比较倾向于自查询。这个视图理由个YSBalance如果ysbalance>0作为条件，会很慢！CREATE OR REPLACE VIEW V_FEE_RECEXESBASEINFO_CY AS
SELECT EXES_CY.EXESID,                                        --费用主键
       EXES_CY.ACCOUNTID,                                     --费用台帐主键
       EXES_CY.CONTAINERSIZEID,                               --箱尺寸编号
       (SELECT CONTAINERSIZE.CONTAINERSIZENAME
          FROM BAS.T_BAS_CONTAINERSIZE CONTAINERSIZE
         WHERE EXES_CY.CONTAINERSIZEID = CONTAINERSIZE.CONTAINERSIZECODE)
       AS CONTAINERSIZENAME,                                  --箱尺寸名称
       EXES_CY.CONTAINERTYPEID,                               --箱型编号
       (SELECT CONTAINERTYPE.CONTAINERTYPENAME
          FROM BAS.T_BAS_CONTAINERTYPE CONTAINERTYPE
         WHERE EXES_CY.CONTAINERTYPEID = CONTAINERTYPE.CONTAINERTYPECODE)
       AS CONTAINERTYPENAME,                                  --箱型名称
       EXES_CY.CURRENCYID,                                    --币种编号
       (SELECT CURRENCY.CURRENCYNAME
          FROM BAS.T_BAS_CURRENCY CURRENCY
         WHERE EXES_CY.CURRENCYID = CURRENCY.CURRENCYCODE)
       AS CURRENCYNAME,                                       --币种名称
       (SELECT CURRENCY.CURRENCYNAME
          FROM BAS.T_BAS_CURRENCY CURRENCY
         WHERE EXES_CY.CURRENCYID = CURRENCY.CURRENCYCODE)
       AS OLDCURRENCYNAME,                                    --币种名称(在窗口收费中用到)
       EXES_CY.FREIGHTITEMID,                                 --费用项目编号
       (SELECT FREIGHTITEM.FREIGHTITEMNAME
               FROM BAS.T_BAS_FREIGHTITEM FREIGHTITEM
                    WHERE FREIGHTITEM.FREIGHTITEMCODE = EXES_CY.FREIGHTITEMID)
       AS FreightItemName,                                     --费用项目名称
       EXES_CY.RECEIPTPAYTYPEID,                               --收付类型主键
       (SELECT RECEPTPAYTYPE.RECEPTPAYTYPENAME
          FROM BAS.T_BAS_RECEPTPAYTYPE RECEPTPAYTYPE
         WHERE EXES_CY.RECEIPTPAYTYPEID = RECEPTPAYTYPE.RECEPTPAYTYPEID)
       AS RECEIPTPAYTYPENAME,                                  --收付类型名称
       EXES_CY.FREIGHTCUSTOMERID,                             --往来单位编号
       (SELECT CUSTOMER.CUSTOMERCHINESENAME
          FROM CRMS.T_CRM_CUSTOMER CUSTOMER
         WHERE EXES_CY.FREIGHTCUSTOMERID = CUSTOMER.CUSTOMERID)
       AS FREIGHTCUSTOMERNAME,                                         --往来单位名称
       (CASE EXES_CY.ISVALIDATE WHEN 0 THEN '未确认' ELSE '已确认' END)
       AS ISVALIDATE,                                         --确认状态
       EXES_CY.PRICE,                                         --单价
       EXES_CY.NUMBERS,                                       --数量
       EXES_CY.AMOUNT,                                        --金额(发票专用)
       EXES_CY.DERATEAMOUNT,                                  --减免金额
       EXES_CY.PAYTYPE,                                       --收付类型
       EXES_CY.MEMO,                                          --备注
       EXES_CY.SFTIME,                                        --预结时间
       EXES_CY.INPUTTIME,                                     --录入时间
       EXES_CY.OBJECTNO,                                      --引发对象编号
       exes_cy.taskid,
       EXES_CY.INPUTPEOPLEID,                                 --录入人编号
       (SELECT SECURITY.SECURITYNAME
          FROM SYSTEMADMIN.SYSTEMFRAMEWORKSECURITY SECURITY
         WHERE EXES_CY.INPUTPEOPLEID = SECURITY.SECURITYID)
       AS INPUTPEOPLENAME,                                    --录入人名称
       EXES_CY.CONFIRMPEOPLEID,                               --确认人编号
       (SELECT COMFIRMSECURITY.SECURITYNAME
          FROM SYSTEMADMIN.SYSTEMFRAMEWORKSECURITY COMFIRMSECURITY
         WHERE EXES_CY.CONFIRMPEOPLEID = COMFIRMSECURITY.SECURITYID)
       AS CONFIRMPEOPLENAME,                                  --确认人名称
       EXES_CY.CAUSEOBJECTTYPEID,                             --引发对象类型主键
       EXES_CY.FREIGHTUNITID,                                 --计费单位主键
       (SELECT FREIGHTUNIT.FREIGHTUNITNAME
          FROM BAS.T_BAS_FREIGHTUNIT FREIGHTUNIT
         WHERE EXES_CY.FREIGHTUNITID = FREIGHTUNIT.FREIGHTUNITID)
       AS FREIGHTUNITNAME,                                    --计费单位名称
       (SELECT FREIGHTTYPE.FreightTypeCode
                FROM BAS.T_BAS_FREIGHTTYPE FREIGHTTYPE
                     WHERE FREIGHTTYPE.FREIGHTTYPECODE = (SELECT FREIGHTITEM.FREIGHTTYPECODE
                                                                 FROM BAS.T_BAS_FREIGHTITEM FREIGHTITEM
                                                                      WHERE FREIGHTITEM.FREIGHTITEMCODE = EXES_CY.FREIGHTITEMID))
                                                                      as FreightTypeCode,
        (SELECT FREIGHTTYPE.FreightTypeName
                FROM BAS.T_BAS_FREIGHTTYPE FREIGHTTYPE
                     WHERE FREIGHTTYPE.FREIGHTTYPECODE = (SELECT FREIGHTITEM.FREIGHTTYPECODE
                                                                 FROM BAS.T_BAS_FREIGHTITEM FREIGHTITEM
                                                                      WHERE FREIGHTITEM.FREIGHTITEMCODE = EXES_CY.FREIGHTITEMID))
                                                                      as FreightTypeName,
      DECODE((SELECT ROUND(SUM(EXESRECEIPT.RATE*EXESRECEIPT.AMOUNT),2) as RECEIPTTOTAL
                     FROM T_FEE_ExesReceipt EXESRECEIPT WHERE EXESRECEIPT.RECEIPTEXESID=EXES_CY.EXESID
                          GROUP BY EXESRECEIPT.RECEIPTEXESID),0,'未收款',EXES_CY.amount-nvl(exes_cy.derateamount,0),'已收款',NULL,'未收款','部分收款') RECEIPTSTATE,       DECODE((SELECT ROUND(SUM(EXESINVOICE.AMOUNT),2) AS INVOICETOTAL
                      FROM T_FEE_EXESINVOICE EXESINVOICE WHERE EXESINVOICE.EXESID=EXES_CY.EXESID
                           GROUP BY EXESINVOICE.EXESID),0,'未缮制',EXES_CY.amount-nvl(exes_cy.derateamount,0),'已缮制',NULL,'未缮制','部分缮制') INVOICESTATE,
       (EXES_CY.Amount-NVL((SELECT ROUND(SUM(EXESRECEIPT.RATE*EXESRECEIPT.AMOUNT),2) as RECEIPTTOTAL
                                   FROM T_FEE_ExesReceipt EXESRECEIPT WHERE EXESRECEIPT.RECEIPTEXESID=EXES_CY.EXESID
                                        GROUP BY EXESRECEIPT.RECEIPTEXESID),0)-nvl(exes_cy.derateamount,0))
       AS YSBALANCE,                                          --收款余额
       (EXES_CY.Amount-NVL((SELECT ROUND(SUM(EXESINVOICE.AMOUNT),2) AS INVOICETOTAL
                                   FROM T_FEE_EXESINVOICE EXESINVOICE WHERE EXESINVOICE.EXESID=EXES_CY.EXESID
                                        GROUP BY EXESINVOICE.EXESID),0)-nvl(exes_cy.derateamount,0))
       AS INVOICEBALANCE,                                     --发票余额
      '1' AS Busitypeid,
       1 AS FEESOURCETYPE,                                    --费用子类型主键
       '箱管委托费' AS FEESOURCETYPENAME,                     --费用子类型名称
       '10' AS AccountAge,                                    --账龄
       (CASE WHEN EXES_CY.ratesettlementway = 0 THEN '现结'
             WHEN EXES_CY.ratesettlementway = 1 THEN '月结'
             ELSE NULL END)
       AS RATESETTLEMENTWAY,                                  --结算方式
       EXES_CY.RATESETTLEMENTWAY AS RATESETTLEMENTWAYID,
       EXES_CY.Activitynumber,
       (CASE EXES_CY.ISLOCK WHEN 0 THEN '未锁定' ELSE '已锁定' END)
       AS ISLOCK,                                             --明细费用锁定状态
       EXES_CY.LOCKPEOPLEID,                                  --单条费用锁定人编号
       (SELECT SECURITY.SECURITYNAME
          FROM SYSTEMADMIN.SYSTEMFRAMEWORKSECURITY SECURITY
         WHERE EXES_CY.LOCKPEOPLEID = SECURITY.SECURITYID)
       AS LOCKPEOPLENAME,                                     --单条费用锁定人名称
       EXES_CY.LOCKTIME,                                      --单条费用锁定时间
       EXES_CY.VALIDATETIME                                   --费用确认时间
FROM T_FEE_EXES_CY EXES_CY
WHERE EXES_CY.PAYTYPE = 0

调试易

关于oracle视图查询速度的问题

解决方案 »