[oracle]与子查询的查询执行可怕

标签: sql Oracle
发布时间: 2017/4/15 18:26:10
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有以下查询的性能的问题,我想要一些有关 saleslines 信息和每 salesline 我想要找出最后一个日期是在股票被接受︰

SELECT        XAL_SUPERVISOR.SALESTABLE.SALESNUMBER, XAL_SUPERVISOR.SALESTABLE.DEBTORACCOUNT, XAL_SUPERVISOR.SALESTABLE.DELIVERYNAME, 
                     XAL_SUPERVISOR.SALESTABLE.DELIVERYADDRESS3, XAL_SUPERVISOR.SALESTABLE.REQUISNUMBER, XAL_SUPERVISOR.SALESTABLE.CUSTOMERREF, 
                     XAL_SUPERVISOR.SALESTABLE.ROUTE, XAL_SUPERVISOR.SALESTABLE.ROUTENUMBER, XAL_SUPERVISOR.SALESTABLE.CMPVWSTATUS, 
                     XAL_SUPERVISOR.SALESTABLE.CMPLOGISTIEK, XAL_SUPERVISOR.SALESTABLE.USVEHICLE, XAL_SUPERVISOR.SALESTABLE.ELCSALSTCALL, 
                     XAL_SUPERVISOR.SALESTABLE.ELCSALSTOK, XAL_SUPERVISOR.SALESTABLE.ELCEDICODE, XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER, 
                     XAL_SUPERVISOR.STOCKTABLE.ITEMNAME, XAL_SUPERVISOR.SALESTRANS.QTYORDERED, XAL_SUPERVISOR.SALESTRANS.STOCKLOC AS REGELLOC, 
                     XAL_SUPERVISOR.STOCKTABLE.STOCKLOC AS STDLOC, XAL_SUPERVISOR.SALESTABLE.DELIVERYDATE, XAL_SUPERVISOR.SALESTABLE.DATASET, 
                     XAL_SUPERVISOR.SALESTABLE.CMPCORRECTIE, XAL_SUPERVISOR.SALESTRANS.ELCORGQTYORDERED AS ORG_BESTELD, 
                     XAL_SUPERVISOR.STOCKTABLE.CMPVERVALLEN,
                         (SELECT        (SUM(STS.ENTEREDQTY) + SUM(STS.RECEIVED) - SUM(STS.DRAWN))
                           FROM            XAL_SUPERVISOR.STOCKSUM STS
                           WHERE        STS.ITEMNUMBER = XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER AND STS.DATASET = 'CMP'
                           GROUP BY STS.ITEMNUMBER) AS VOORRAAD,
                         (SELECT        SUM(STS.ORDERED)
                           FROM            XAL_SUPERVISOR.STOCKSUM STS
                           WHERE        STS.ITEMNUMBER = XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER AND STS.DATASET = 'CMP'
                           GROUP BY STS.ITEMNUMBER) AS BESTELD,
                         (SELECT        SUM(STS.RESERVPHYSICAL)
                           FROM            XAL_SUPERVISOR.STOCKSUM STS
                           WHERE        STS.ITEMNUMBER = XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER AND STS.DATASET = 'CMP'
                           GROUP BY STS.ITEMNUMBER) AS GERESERVEERD,
                         (SELECT        DDT.QTY
                           FROM            XAL_SUPERVISOR.DEBDLVTRANS DDT
                           WHERE        DDT.TRANSID = XAL_SUPERVISOR.SALESTRANS.TRANSID AND DDT.DATASET = 'CMP') AS PAKBONAANTAL,
                         (SELECT        DIT.QTY
                           FROM            XAL_SUPERVISOR.DEBINVTRANS DIT
                           WHERE        DIT.TRANSID = XAL_SUPERVISOR.SALESTRANS.TRANSID AND DIT.DATASET = 'CMP') AS FACTUURAANTAL,
                         (SELECT        MAX(ST.DATEPHYSICAL)
                           FROM            XAL_SUPERVISOR.STOCKTRANS ST
                           WHERE        ST.ITEMNUMBER = XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER AND ST.DATASET = 'CMP' AND ST.StatusInFlow < 3 AND ST.DCType = 2) 
                     AS LTSTGELEVERD
FROM            XAL_SUPERVISOR.SALESTABLE, XAL_SUPERVISOR.SALESTRANS, XAL_SUPERVISOR.STOCKTABLE
WHERE        XAL_SUPERVISOR.SALESTABLE.DATASET = XAL_SUPERVISOR.SALESTRANS.DATASET AND 
                     XAL_SUPERVISOR.SALESTABLE.SALESNUMBER = XAL_SUPERVISOR.SALESTRANS.SALESNUMBER AND 
                     XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER = XAL_SUPERVISOR.STOCKTABLE.ITEMNUMBER AND 
                     XAL_SUPERVISOR.SALESTRANS.DATASET = XAL_SUPERVISOR.STOCKTABLE.DATASET AND (XAL_SUPERVISOR.SALESTABLE.DELIVERYDATE = :Leverdatum) AND 
                     (XAL_SUPERVISOR.SALESTABLE.DATASET = 'CMP') AND (XAL_SUPERVISOR.SALESTABLE.CMPCORRECTIE = 0)

这部分减慢 (没有它运行在 < 10 秒)︰

   (SELECT        MAX(ST.DATEPHYSICAL)
                           FROM            XAL_SUPERVISOR.STOCKTRANS ST
                           WHERE        ST.ITEMNUMBER = XAL_SUPERVISOR.SALESTRANS.ITEMNUMBER AND ST.DATASET = 'CMP' AND ST.StatusInFlow < 3 AND ST.DCType = 2) 
                     AS LTSTGELEVERD

当我在 SQL 中运行它再加上我看到它读取主查询,然后停了很长时间才能取上述子查询吗?

解决方法 1:

有一对夫妇的事情,你可以尝试︰

  • 在第四和第五的子查询只是得到一个标量值,所以它们可以被放到查询的主体。它看起来像你正在使用子查询在这里以避免 LEFT JOIN

  • 第一、 第二、 第三和第六子查询可以组合使用公共表表达式 (CTE),否则称为 Oracle WITH 子句。其他的子查询值,它们将会被纳入使用 LEFT JOIN

此外,这出来如果你使用 ANSI join 语法的要容易得多。这里是的答案 (虽然注意到我遗漏了很多的"旁观者"列,所以才会有点紧凑; 您可以添加那些回如果此解决方案适合你):

WITH StkSum AS (
  SELECT
    STS.ITEMNUMBER,
    SUM(STS.ENTEREDQTY) + SUM(STS.RECEIVED) - SUM(STS.DRAWN) AS VOORRAAD,
    SUM(STS.ORDERED) AS BESTELD,
    SUM(STS.RESERVPHYSICAL) AS GERESERVEERD,
    MAX(ST.DATEPHYSICAL) AS LTSTGELEVERD
  FROM XAL_SUPERVISOR.STOCKSUM STS
  INNER JOIN XAL_SUPERVISOR.STOCKTRANS ST ON STS.ITEMNUMBER = ST.ITEMNUMBER
  WHERE STS.DATASET = 'CMP'
    AND ST.DATASET = 'CMP'
    AND ST.StatusInFlow < 3
    AND ST.DCType = 2
)
SELECT
  XAL_SUPERVISOR.SALESTABLE.SALESNUMBER,
  ... all those SALESTABLE and SALESTRANS and STOCKTABLE columns ...,
  StkSum.VOORRAAD,
  StkSum.BESTELD,
  StkSum.GERESERVEERD,
  NVL(XAL_SUPERVISOR.DEBDLVTRANS.QTY, 0) AS PAKBONAANTAL,
  NVL(XAL_SUPERVISOR.DEBINVTRANS.QTY, 0) AS FACTUURAANTAL,
  StkSum.LTSTGELEVERD
FROM XAL_SUPERVISOR.SALESTABLE
INNER JOIN XAL_SUPERVISOR.SALESTRANS ON
  SalesTable.DataSet = SalesTrans.DataSet AND
  SalesTable.SalesNumber = SalesTrans.SalesNumber
INNER JOIN XAL_SUPERVISOR.STOCKTABLE ON
  SalesTrans.ItemNumber = StockTable.ItemNumber AND
  SalesTrans.DataSet = StockTable.DataSet
LEFT OUTER JOIN StkSum ON StkSum.ITEMNUMBER = SalesTrans.ITEMNUMBER
LEFT OUTER JOIN XAL_SUPERVISOR.DEBDLVTRANS DDT ON DDT.TRANSID = SalesTrans.TRANSID
LEFT OUTER JOIN XAL_SUPERVISOR.DEBINVTRANS DIT ON DIT.TRANSID = SalesTrans.TRANSID
WHERE
  (XAL_SUPERVISOR.SALESTABLE.DELIVERYDATE = :Leverdatum) AND 
  (XAL_SUPERVISOR.SALESTABLE.DATASET = 'CMP') AND
  (XAL_SUPERVISOR.SALESTABLE.CMPCORRECTIE = 0) AND
  DDT.DATASET = 'CMP' AND
  DIT.DATASET = 'CMP'

最后,请注意,由于数量的表和列和条件,我会很震惊,如果上述查询是 100%正确。我尽力了,但我最好不可能不够好在这里:)可能会需要 Tweakage。

官方微信
官方QQ群
31647020