Tune the Oracle query

5 pts.
Tags:
Oracle
SELECT a.custno,
a.stockto,
a.dayname,
TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') "WEEKNO",
a.rad,
-- COUNT (NVL (a.load_number_last, a.cono)) "TDY_LOAD_CNT",
SUM (a.load_count) "TDY_LOAD_CNT",
NVL (TO_NUMBER (b.avail), 0) "TDY_CAP",
DECODE (TRUNC (a.rad),
TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (a.custno),
NULL)
"PRIOR_LOAD_CNT"
FROM xxx_ess_scp_ib_cap_order a,
(SELECT cal,
(eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
avail
FROM caldata) b
WHERE a.rad >= TRUNC(SYSDATE) -- this is creating an issue
AND a.rad = b.effdate(+)
AND (a.custno || '-INCAP') = b.cal(+)
GROUP BY a.custno,
a.stockto,
a.rad,
a.weekno,
a.dayname,
b.avail

UNION ALL
-- Dummy Data
SELECT b.custno,
b.stockto,
TO_CHAR (a.dateholder, 'DY') "DAYNAME",
TO_CHAR (a.dateholder, 'IYYY') || TO_CHAR (a.dateholder, 'IW')
"WEEKNO",
a.dateholder "RAD",
0 "TDY_LOAD_CNT",
NVL (TO_NUMBER (c.avail), 0) "TDY_CAP",
DECODE (TRUNC (a.dateholder),
TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),
NULL)
"PRIOR_LOAD_CNT"
FROM xxx_ess_scp_ib_cap_dates a,
( SELECT DISTINCT custno, stockto, MAX (rad) "LAST_RAD"
FROM jms_ess_scp_ib_cap_order
GROUP BY custno, stockto) b,
(SELECT cal,
(eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
avail
FROM caldata) c
WHERE a.dateholder <= b.last_rad
AND a.dateholder = NVL (c.effdate, a.dateholder)
AND (b.custno || '-INCAP') = c.cal(+)
ORDER BY rad;
From the first union WHERE a.rad >= TRUNC(SYSDATE) this trunc is creating an issue: The rows are 392 rows with trunc. If I remove the trunc, the rows are 354 but the query runs in 1 minute instead of 38 minutes.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    You need to review the execution plans of both versions of the query to see what is being done differently when you remove the TRUNC.

    It is very hard for us to imagine what is happening without much more info about the tables, data, indexes, plans, etc.
    84,355 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: