SQL query is kinda slow

I wrote this query a couple weeks ago and it gets the job done, it's just not that fast. I normally write front end JAVA so SQL is not my thing...can you see any places for improvement in this script that might make it run faster....I appreciate the help.

FUNCTION rollup_like_item_history_data (
   p_tamcn        IN VARCHAR2,  
   p_nsn          IN VARCHAR2, 
   p_work_year    IN VARCHAR2,  
   p_work_type_id IN NUMBER
)
  RETURN sys_refcursor
IS
  stmt       VARCHAR2(4000);
  result_cur sys_refcursor;  
BEGIN

    OPEN result_cur FOR
    SELECT DISTINCT 
           jp.id,jp.line_header.mwslin AS mwslin,  
           jp.sor_code, 
           jp.workload_year, 
           jp.line_header.fiscal_year AS fiscal_year,
           nsns.sac, 
           tamcns.tamcn, 
           nsns.nsn,
           DECODE(jp.line_header.nsn_id, 
                 NULL, jp.line_header.nomenclature, 
                 nsns.nomenclature) AS nomenclature,
           jp.line_header.sup AS sup,
           jp.line_header.work_type_id AS work_type_id
      FROM schedules sch, 
           job_plans JP, 
           master_nsn nsns, 
           master_tamcn tamcns, 
           TABLE(tamcns.pgd_group_id) (+) ntab, 
           pgd_groups pgds
     WHERE (nsns.nsn = p_nsn OR p_nsn IS NULL) 
       AND (UPPER(tamcns.tamcn) LIKE UPPER(p_tamcn) OR p_tamcn IS NULL)
       AND (jp.line_header.work_type_id = p_work_type_id OR p_work_type_id IS NULL)
    -- AND p_work_year = ntab.fiscal_year(+)
       AND ntab.pgd_group_id = pgds.id(+) 
       AND jp.line_header.nsn_id = nsns.id(+) 
       AND nsns.tamcn_id = tamcns.id(+)
       AND (    
            (p_work_year IS NULL         
             AND jp.workload_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24), 'YYYY')                   
             AND jp.line_header.fiscal_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24), 'YYYY')     
           )     
           OR    
              (        
               p_work_year is not NULL        
            -- AND p_work_year = ntab.fiscal_year(+)                      
               AND jp.workload_year =  p_work_year                      
               AND jp.line_header.fiscal_year = p_work_year       
              )
           )
       AND JP.REVISION = (select MAX(jp2.revision) 
                            from job_plans jp2
                           WHERE JP.CONTROL_NUMBER = JP2.CONTROL_NUMBER)
       AND  job_plan_pkg.get_last_job_plan_state_type(jp.id) != 1; 

Answers


  • See if you can get rid of any UPPER()'s on indexed columns in the WHERE clause
  • Look into replacing that SELECT MAX() at the end with a DENSE_RANK() = 1 (example)

Need Your Help

Using json to read from api and store values in arrays

php json api curl

I have a json file, containing the following:

JSONArray only returns 1 put?

arrays json jsonobject

i have a function which returns a JSON object