SQL查询有点慢

Question

我在几周前写了这个查询，它完成了工作，只是没有那么快。我通常写前端JAVA，所以SQL不是我的thing...can，你可以在这个脚本中看到任何可以改进的地方，可以让它运行得更快……我很感谢你的帮助。

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; 

Answer 1

当我更快地从clause...went中删除"schedules sch“时，我忘记了我删除了它的引用。

你们SQL的家伙居然没有注意到……:)

Answer 2

• 查看是否可以去掉WHERE子句中索引列上的任何大写()，
• 考虑用DENSE_RANK() =1 DENSE_RANK()替换结尾的SELECT MAX()

Answer 3

这部分

AND JP.REVISION = (select MAX(jp2.revision) 
                            from job_plans jp2
                           WHERE JP.CONTROL_NUMBER = JP2.CONTROL_NUMBER)

将对每条记录求值，请使用inner而不是这样：

join (select control_number, max(revision) 
      from job_plans
      group by control_number) jp2
on jp.control_number = jp2.control_number 

你也可以在SQL Developer中使用你的查询和创建执行计划，查看索引的用法，也许你的查询在选择性较低的查询中使用了全表扫描。