I have a query that should return around 10000 rows. The db itself is very large. I have run a simple query and it returned a result in less than 3 seconds. But when a more complex code it takes way too long.
In my code I have done a nested select and a case statement. However, when I run my code it takes over an hour to return a result. What can I do to the code that would decrease this execution time.
SELECT ticker_symb, day_sum_eff, cusip,
clos_prc,
nclos_prc,
case
when clos_prc is null and nclos_prc is not null
then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip))
when clos_prc is not null and nclos_prc is null
then (LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip))
else NULL
end DIFF
FROM (SELECT
day_sum_eff,
cusip,
ticker_symb,
clos_prc,
nclos_prc,
case
when clos_prc is null and nclos_prc is not null
then (nclos_prc - LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip))
when clos_prc is not null and nclos_prc is null
then LEAD( nclos_prc ignore nulls) OVER (ORDER BY cusip)- LAG( nclos_prc ignore nulls) OVER (ORDER BY cusip)
else NULL
end DIFF
from MKTDATA.MARKET_DAILY_SUMMARY
WHERE day_sum_eff >= '1-JUN-2017' and
day_sum_eff <= '10-JUN-2017' )
order by day_sum_eff_,fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY;
EXCUTION PLAN TABLE
PLAN_TABLE_OUTPUT
Plan hash value: 831959278
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | WINDOW SORT PUSHED RANK | |
| 3 | WINDOW SORT | |
| 4 | PARTITION RANGE SINGLE| |
| 5 | TABLE ACCESS FULL | MARKET_DAILY_SUMMARY |
----------------------------------------------------------
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT
ticker_symb,
day_sum_eff,
cusip,
clos_prc,
nclos_prc,
CASE
WHEN clos_prc IS NULL
AND nclos_prc IS NOT NULL THEN (nclos_prc - LAG(nclos_prc IGNORE NULLS) OVER (ORDER
BY
cusip))
WHEN clos_prc IS NOT NULL
AND nclos_prc IS NULL THEN (LEAD(nclos_prc IGNORE NULLS) OVER (ORDER
BY
cusip) - LAG(nclos_prc IGNORE NULLS) OVER (ORDER
BY
cusip))
ELSE NULL END DIFF
FROM
(SELECT
MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff,
MKTDATA.MARKET_DAILY_SUMMARY.cusip,
MKTDATA.MARKET_DAILY_SUMMARY.ticker_symb,
MKTDATA.MARKET_DAILY_SUMMARY.clos_prc,
MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc,
CASE
WHEN MKTDATA.MARKET_DAILY_SUMMARY.clos_prc IS NULL
AND MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IS NOT NULL THEN (MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc - LAG(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER
BY
MKTDATA.MARKET_DAILY_SUMMARY.cusip))
WHEN MKTDATA.MARKET_DAILY_SUMMARY.clos_prc IS NOT NULL
AND MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IS NULL THEN LEAD(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER
BY
MKTDATA.MARKET_DAILY_SUMMARY.cusip) - LAG(MKTDATA.MARKET_DAILY_SUMMARY.nclos_prc IGNORE NULLS) OVER (ORDER
BY
MKTDATA.MARKET_DAILY_SUMMARY.cusip)
ELSE NULL END DIFF
FROM
MKTDATA.MARKET_DAILY_SUMMARY
WHERE
MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff >= '1-JUN-2017'
AND MKTDATA.MARKET_DAILY_SUMMARY.day_sum_eff <= '10-JUN-2017')
ORDER BY
day_sum_eff_,
fmr_iss_cusip OFFSET 0 ROWS FETCH NEXT 3 ROW ONLY