query optimization [message #410575] |
Mon, 29 June 2009 01:37  |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Hi,
This query takes much time to execute around 10 minutes.
Please help to optmize the query.
I cannot put structure because of security reason.
SELECT prz.*, (SELECT COUNT(*) AS qty_a
FROM runs w
WHERE w.PrizeID = prz.PrizeID
AND w.StatusCode NOT IN ('R', 'X')) AS QtyAwarded,
(SELECT COUNT(*) AS qty_v
FROM runs w
WHERE w.PrizeID = prz.PrizeID
AND w.StatusCode = 'V') AS QtyVerified,
(SELECT COUNT(*) AS qty_o
FROM selected_items oi JOIN catalog_items ci ON (ci.ItemID = oi.ItemID)
WHERE ci.PrizeID = prz.PrizeID
AND oi.ItemStatus IN ('ORDERED','CONFIRMED')
GROUP BY oi.ItemID
ORDER BY qty_o ASC LIMIT 1) AS QtyOrdered,
(SELECT COUNT(*) AS qty_s
FROM runs w
WHERE w.PrizeID = prz.PrizeID
AND w.StatusCode = 'F') AS QtyShipped,
(SELECT COUNT(*) AS qty_e
FROM selected_items oi JOIN catalog_items ci
ON (ci.ItemID = oi.ItemID)
WHERE ci.PrizeID = prz.PrizeID
AND oi.ItemStatus IN ('REJECTED')
GROUP BY oi.ItemID ORDER BY qty_e ASC LIMIT 1) AS QtyExceptioned
FROM gifts prz ,
WHERE prz.PromotionID=2709;
Thanks,
[Updated on: Mon, 29 June 2009 01:44] Report message to a moderator
|
|
|
|
Re: query optimization [message #410598 is a reply to message #410575] |
Mon, 29 June 2009 03:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you can't give us the information that we need, then you're probsbly not going to get the help you want - sad but true.
Your problem is probably caused by you running each of your SELECT clause queries for every single row that you return.
I'd be very tempted to rewrite your FROM clause asFROM gifts prz
,runs w
,(select *
from selected_items oi
,catalog_items ci
where ci.itemid = oi.itemid) oi
WHERE prz.PromotionID=2709
and prz.prizeid = w.prizeid(+)
and prz.prizeid = oi.prizeid(+); and use analytic functions to do the totalling, or group the whole thing by prz.prizeid and use normal aggregate functions.
What is this LIMIT clause that you're using in the Order by?
|
|
|
|