Home » SQL & PL/SQL » SQL & PL/SQL » Help with query (first_value)
Help with query (first_value) [message #217035] Wed, 31 January 2007 07:25 Go to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
This query is actually embedded in a create view statement, but for the purposes of this thread I wish to just focus on the select statement. I removed a lot of things just to make this query as simple as possible, so you may find syntax errors or just inconsistencies within the query. But what I am working with does work and I am just looking for a new or modified approach to my problem.

The basic query selects data from the EDITS table, where the MEDITS EID equals the EDITS EID.

However, I need to perform a join to the EFS and FNAME tables to get data. This relationship is one to many, but I wish to only return one record per EDITS ID. In doing research I found the FIRST_VALUE function. I implemented this and everything works fine. The problem is that when choosing data from this first record, I need to perform the FIRST_VALUE function on each column I wish to retrieve...and of course each time I use this function I need to re-type the sort.

This is very expensive and is causing very large times when selecting data from this statement. Is there a way for me to select the ID, IDFN, FCC, DISNAME and NAME data while only performing the FIRST_VALUE and sort once, or is there even a better way for me to approach this problem?

Thanks in advance for any help and ideas!

SELECT  
   ED.ID,
   ED.TYP,
   ED.DAT,
   CASE    
      WHEN Q2.FCC IS NULL    
         THEN GET_FCC(ED.ID) 
         ELSE Q2.FCC 
   END          AS PRIFCC,
   Q2.IDEF       AS PRIIDEF,
   Q2.IDFN       AS PRIIDFN,
   Q2.DISNAME       AS PRIDISNAME,
   Q2.NAME       AS PRINAME  
FROM 
   EDITS ED 
      JOIN (
         SELECT 
            DISTINCT(ED.ID), 
            FIRST_VALUE(Q1.ID) 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               )       AS IDEF, 
            FIRST_VALUE(Q1.IDFN) 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               ) AS IDFN, 
            FIRST_VALUE(Q1.DISNAME) 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               ) AS DISNAME, 
            FIRST_VALUE(Q1.NAME) 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               ) AS NAME, 
            FIRST_VALUE(Q1.FCC) 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               ) AS FCC    
         FROM  
            EDITS ED      
               LEFT OUTER JOIN (   
                  SELECT  
                     ED.EID ,      
                     EF.ID, 
                     EF.FLG, 
                     EF.FCC, 
                     EF.TYP, 
                     EF.IDFN, 
                     FN.DISNAME, 
                     FN.NAME  
                  FROM    
                     EDITS ED,   
                     REL R,   
                     EFS EF 
                        LEFT OUTER JOIN 
                           FNAME FN 
                              ON EF.IDFN = FN.ID 
                  WHERE   
                     R.TID = ED.EID AND    
                     R.TYP = 2 AND    
                     EF.TID = R.TID AND 
                     EF.TGID = R.TGID 
                  )   Q1 ON Q1.EID = ED.EID,      
            MEDITS ME 
         WHERE  
            ME.EID = ED.EID  
      )   Q2 ON Q2.ID = ED.ID,  
   MEDITS ME  
WHERE 
   ME.EID = ED.EID

Re: Help with query (first_value) [message #217053 is a reply to message #217035] Wed, 31 January 2007 08:57 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member




What is the explain plan ?


Post the actual "very expensive" query and its autotrace traceonly output.

Srini



Re: Help with query (first_value) [message #217076 is a reply to message #217035] Wed, 31 January 2007 10:40 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Change the inner query to:

(SELECT
ID,
IDFN,
DISNAME,
FCC
FROM
    (SELECT
          ID,
          IDFN,
          DISNAME,
          FCC,
          ROW_NUMBER() 
               OVER(
                  PARTITION BY ED.ID   
                     ORDER BY  
                        CASE Q1.IDFN 
                           WHEN NULL    THEN 2 
                                 ELSE 1 
                        END,    
                        CASE Q1.FLG   
                           WHEN 'P'    THEN 1   
                           WHEN 'S'    THEN 2   
                                 ELSE 3 
                        END, 
                        CASE SUBSTR(Q1.FCC,1,1)   
                           WHEN 'R'    THEN 1   
                           WHEN 'L'    THEN 2   
                           WHEN 'K'    THEN 3      
                           WHEN 'P'    THEN 4 
                        END, 
                        CASE Q1.TYP   
                           WHEN 'M'    THEN 1   
                           WHEN 'C'    THEN 2   
                                 ELSE 3 
                        END
               ) AS rn         
         FROM  EDITS ED)
WHERE RN=1)
Previous Topic: Using Binds in Dynamic SQL
Next Topic: SQL beeded
Goto Forum:
  


Current Time: Mon Dec 05 06:33:16 CST 2016

Total time taken to generate the page: 0.16866 seconds