-
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;
Last edited by gandolf989; 04-02-2014 at 03:27 PM.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
1) Format your code using the "code" tags
2) Post the explain plan
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by LKBrwn_DBA
1) Format your code using the "code" tags
2) Post the explain plan
This is the best formatting I could do.. can we focus on tuning..there is explain plan right now..
with
Page_Info as (
Select age_number as page_number , age_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 (roductCode)
)
, 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
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
I formatted it and forum removed all the formatting...
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
I added the formatting for you. When you are adding a post you can select choose "go advanced" and use the hash # tag
to tag your code. You should probably post the explain plan and show the indexes on the table including the primary key.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|