Amigos - how to you tune this query? this is not pl/sql but dba job..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Amigos - how to you tune this query? this is not pl/sql but dba job..

Threaded View

  1. #1
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510

    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 04:27 PM.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width