Amigos - how to you tune this query? this is not pl/sql but dba job..
THIS IS WITH QUERY WITH SUBQUERY FACTORING.. ANY ADVICE APPRECIATED...
Code:
WITH Page_Info AS
( SELECT :page_number AS page_number , :page_limit AS page_limit
FROM Dual ) ,
Selected_Products AS
( SELECT productCode, productCode_Find__model AS modelID ,
productCode_Find__configCode AS configCode
FROM AppleYield_ProductCode_Find_V
WHERE ProductCode NOT IN (:blackList)
AND ProductCode IN (:productCode)) ,
ProductTask_Lines_W AS
( SELECT /*+ FULL(pt_MV) PARALLEL(pt_MV, 3) */
pt_MV.startedTimestamp , pt_MV.buildLineID ,
MIN(sp.productCode) AS productCode
FROM INDIGO.productTask_MV pt_MV -- use old MV table for better performance, and to
-- get today's options refreshed every hour
JOIN Selected_Products sp
ON sp.modelID = pt_MV.modelID
WHERE 1 = 1
AND pt_MV.buildLineID IS NOT NULL
AND pt_MV.startedTimestamp BETWEEN to_date(:startDate,'MM/DD/YYYY') AND
to_date(:endDate,'MM/DD/YYYY')
GROUP BY pt_MV.startedTimestamp, pt_MV.buildLineID ),
LINE_NAME AS
( SELECT *
FROM ( SELECT DISTINCT l.name AS Line_ID, l.lineType
FROM ProductTask_Lines_W ptl_V
JOIN INDIGO.buildLine bl
ON bl.buildLineID = ptl_V.buildLineID
JOIN INDIGO.line l
ON l.lineID = bl.siteLineID )
WHERE 1=1
ORDER BY upper(Line_ID)),
LINE_NAME_T AS
( SELECT line_id, Row_Number() over(order by UPPER(line_id)) AS rownumber,
COUNT(*) over () AS total
FROM LINE_NAME )
SELECT line_id, total
FROM LINE_NAME_T, Page_Info
WHERE rownumber > (page_number-1) * page_limit
AND NOT rownumber > page_number * page_limit;
