选择中的子查询将导致我的结果不返回。

Question

你好，我有一个问题，一个非常复杂的查询，我没有写查询，但我必须维护它。

我有下面的超长查询，它可以工作一段时间(缓慢但很好)，但是在上个月的一些时候它停止了工作。我们没有得到一个错误，它只是超时返回结果。

现在，如果我在下面运行这个有趣的查询，在选择条件中删除第一个子查询，结果将在136秒内返回前50。

如果我把它放在里面，几个小时后就会超时。还要注意的是，如果我在select语句中接受子查询并在它自己的硬编码上运行它，那么传入它的任何值都会在0.004秒内返回结果。

如果我将其全部放在其中，但是转到这个长查询中的核心查询，并对SDDOCO施加限制，那么它将返回一个结果，我将在94秒内得到结果，而在select中是否有子查询并不重要。

我认为结果之一是导致问题并使查询陷入困境，但我想不出一个好方法来找出哪一项会导致这种情况。当它返回时，我的结果集是47k记录。

任何建议，我可以看看，或如何我可以进一步调查，将不胜感激。

SELECT
SUM((GL.GLAA/100) * (CAST(COALESCE((Select CXCRR from PRODDTA.F0015 F2 WHERE CXEFT = (SELECT MAX(CXEFT) FROM PRODDTA.F0015 F3 WHERE F3.CXEFT <= GL.GLDGJ) and CXCRCD = GL.GLCRCD AND CXCRDC ='USD'), 1) AS NUMERIC(15,4)))) TEST1,
SUM((GL.GLAA/100)) test2
FROM (SELECT MAX(SDAN8) SDAN8, MAX(SDMCU) SDMCU, MAX(SDDOCO) SDDOCO, MAX(SDDOC) SDDOC, MAX(SDSHAN) SDSHAN, CASE WHEN NVL(TRIM(TMURRF),' ') = ' ' then SDURRF ELSE TMURRF END SDURRF, MAX(SDDCTO) SDDCTO, MAX(SDDGL) SDDGL, MAX(SDASN) SDASN
    , SUM(CASE WHEN IMGLPT like 'FG%' THEN (CASE WHEN NOT (UMCONV IS NULL) THEN SDSOQS/100 * UMCONV/10000000 else SDSOQS/100 END) ELSE 0 END) AS SDSOQS
    , SUM(CASE WHEN IMGLPT = 'FG04' THEN (CASE WHEN NOT (UMCONV IS NULL) THEN SDSOQS/100 * UMCONV/10000000 ELSE SDSOQS/100 END) ELSE 0 END) AS AER_SDSOQS
    , SUM(CASE WHEN IMGLPT like 'FG%' THEN (CASE WHEN SDSRP5 = '527' THEN (CASE WHEN NOT (UMCONV IS NULL) THEN SDSOQS/100 * UMCONV/10000000 ELSE SDSOQS/100 END) ELSE 0 END) ELSe 0 END) AS MDJ_SDSOQS
    , MAX(SDIVD) SDIVD, MAX(SDADDJ) SDADDJ
    , SUM(CASE WHEN SDWTUM = 'LB' THEN SDITWT WHEN UCCONV is not null then SDITWT*(UCCONV/10000000) WHEN CONV is not null THEN SDSOQS*CONV*10 ELSE 0 END)/10000 AS WEIGHT
    ,SUM(CASE WHEN IMGLPT like 'FG%' THEN 
      CASE 
            WHEN SDWTUM = 'LB' THEN  SDITWT 
            WHEN UCCONV IS NOT NULL THEN SDITWT*(UCCONV/10000000)
            WHEN CONV is not null THEN SDSOQS*CONV*10
            ELSE 0 END
    ELSE 0
    end)/10000 as FG_WEIGHT,
    SUM(CASE WHEN IMGLPT = 'FG04' THEN 
      CASE 
            WHEN SDWTUM = 'LB' THEN  SDITWT 
            WHEN UCCONV IS NOT NULL THEN SDITWT*(UCCONV/10000000)
            WHEN CONV is not null THEN SDSOQS*CONV*10
            ELSE 0 END
    ELSE 0
    end)/10000 as AER_WEIGHT,
    SUM(CASE WHEN SDSRP5 = '527' THEN 
      CASE 
            WHEN SDWTUM = 'LB' THEN  SDITWT 
            WHEN UCCONV IS NOT NULL THEN SDITWT*(UCCONV/10000000)
            WHEN CONV is not null THEN SDSOQS*CONV*10
            ELSE 0 END
    ELSE 0
    end)/10000 as MDJ_WEIGHT,
    SUM(CASE WHEN IMGLPT = 'FG07' THEN 
      CASE 
            WHEN SDWTUM = 'LB' THEN  SDITWT 
            WHEN UCCONV IS NOT NULL THEN SDITWT*(UCCONV/10000000)
            WHEN CONV is not null THEN SDSOQS*CONV*10
            ELSE 0 END
    ELSE 0
    end)/10000 as MDJ_3rdParty_WEIGHT, max(SDCARS) SDCARS
  FROM PRODDTA.F42119
  left join proddta.F554202x on trim(SDURRF) = trim(TMUSRRSV1) and SDDOCO = TMDOCO
  LEFT JOIN PRODDTA.F4101 ON IMITM = SDITM
  LEFT JOIN PRODDTA.F41002 ON UMITM = SDITM AND SDMCU = UMMCU AND SDUOM = UMUM AND UMRUM = imuom1
  left join PRODDTA.F41003 on UCUM = SDWTUM and UCRUM = 'LB'
  Left JOin (SELECT UMMCU as MCU, UMITM as ITM, UMUM as UM, max(CASE WHEN UMRUM = 'LB' THEN UMCONV/10000000 ELSE UMCONV/10000000 * UCCONV/10000000 END) AS CONV 
    FROM PRODDTA.F41002
    LEFT JOIN PRODDTA.F41003
      ON UMRUM = UCUM
    WHERE (UCRUM = 'LB' OR UMRUM = 'LB')
    GROUP BY UMMCU, UMITM, UMUM) CONV2
  ON MCU = SDMCU AND ITM = SDITM AND UM = SDUOM
    WHERE SDLNTY = 'S' 
    AND SDSOQS > 0 
    and not ((SDLTTR = 980 AND SDNXTR = 999) OR SDSOCN = SDUORG) 
    and SDSRP1 <> 'BLK' 
    and not exists (select SDDOCO from proddta.F42119 GA where SDADDJ >= FISCALPERIODSTART(14,1) and SDADDJ <= FISCALPERIODEND(14,12) and SDUOM = 'GA' and F42119.SDDOCO = GA.SDDOCO)
    and SDDOCO in (1230256,1227461,1230628,1225291,1225297,1231601,1242703,1248671,1249556,1244905)
  GROUP BY CASE WHEN NVL(TRIM(TMURRF),' ') = ' ' then SDURRF ELSE TMURRF END) DTL
Left JOIN PRODDTA.F0101 BT ON BT.ABAN8 = DTL.SDAN8
Left JOIN PRODDTA.F0101 ST ON ST.ABAN8 = DTL.SDSHAN
INNER JOIN PRODDTA.F0911 GL
ON TRIM(GL.GLEXR) = TRIM(DTL.SDURRF)
WHERE GLDCT = 'PV' AND GLDGJ >= KIKDATETOJUL(KIKE1JULTODATE(FISCALPERIODSTART(14,1)) - 90) and GLDGJ <= KIKDATETOJUL(KIKE1JULTODATE(FISCALPERIODEND(14,12)) + 90) AND GLOBJ IN ('5025','5026') AND  GLLT ='AA' AND GLEXTL <> 'AM' AND GLEXR <> ' ' and GLRE = ' '
GROUP BY GL.GLEXR, GL.GLDCT, 
CASE WHEN SDDCTO IN ('ST','SJ') THEN CAST(SDSHAN AS NCHAR(12)) ELSE TRIM(SDMCU) END, DTL.SDASN, DTL.SDDOC, DTL.SDDOCO, DTL.SDDCTO, DTL.SDSOQS, DTL.AER_SDSOQS, DTL.MDJ_SDSOQS, DTL.SDSHAN, DTL.SDAN8, SDURRF, GL.GLSUB, Weight, FG_Weight, AER_WEIGHT, MDJ_WEIGHT, MDJ_3rdParty_WEIGHT

Answer 1

我们在select语句之前提供了下面的代码，这改变了Oracle DB优化查询的方式。

alter session set optimizer_index_caching=0;
alter session set optimizer_index_cost_adj=80;

这个解决了问题。