sql - 具有相同表的多个连接的存储过程

Question

我有这个程序试图在 oracle 环境（11.2）中加载 table5，它与相同的表（每个都有 500 万条记录）连接超过 3 次，并且需要 2 个多小时。

create or replace PROCEDURE PROC1 AS  
  BEGIN  
        INSERT INTO table4  
 SELECT  
        lt_sn_issues.DA_SECTOR,  
        lt_sn_issues.DA_REGION,  
        tmp_ft_lt_scan_id.PROJ,  
        tmp_ft_lt_scan_id.SOL,  
        COUNT(ft_sn_issues.ISS_ID),  
        COUNT(DISTINCT ft_sn_issues.FL_ID),  
        COUNT(DISTINCT ft_fl_scans.FL_ID),  
        PROJ_files_count.COUNT,  
        COUNT(lt_sn_issues.ISS_ID),  
        COUNT(DISTINCT lt_sn_issues.FL_ID),  
        COUNT(DISTINCT lt_fl_scans.FL_ID),  
        COUNT(pr_sn_issues.ISS_ID),  
        COUNT(DISTINCT pr_sn_issues.FL_ID),  
        COUNT(DISTINCT pr_fl_issues.FL_ID),  
        CURRENT_DATE  
      FROM  
        tmp_ft_lt_scan_id  
        JOIN table1 ft_sn_issues ON ft_sn_issues.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        JOIN table1 lt_sn_issues ON lt_sn_issues.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        LEFT JOIN table1 pr_sn_issues  
          ON pr_sn_issues.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        JOIN table2@dblink ft_fl_scans ON ft_fl_scans.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        JOIN table2@dblink lt_fl_scans ON lt_fl_scans.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        LEFT JOIN table2@dblink pr_fl_issues ON pr_fl_issues.SCAN_ID = tmp_ft_lt_scan_id.FIRST_SCAN_ID  
        JOIN (  
               SELECT  
                 FL_INFOR.PROJ              PROJ,  
                 FL_INFOR.SOL              SOL,  
                 COUNT(DISTINCT FL_INFOR.PATH) COUNT  
               FROM FL_INFOR@DBLINK  
               GROUP BY  FL_INFOR.PROJ,L_INFOR.SOL  
            ) PROJ_files_count  
          ON PROJ_files_count.PROJ = tmp_ft_lt_scan_id.PROJ   
          where rownum<=100  
      GROUP BY  
        lt_sn_issues.DA_SECTOR,  
        lt_sn_issues.DA_REGION,  
        tmp_ft_lt_scan_id.PROJ,  
        tmp_ft_lt_scan_id.SOL,  
       PROJ_files_count.COUNT;

标签： sqloracleperformanceplsqlquery-optimization

Answer 1

如果问题是性能，首先将索引添加到tmp_ft_lt_scan_id.FIRST_SCAN_ID列。