简介
大家好，我是一个Oracle SQL新手，主要来自Python。我有一个大的过程，我将在下面通过一个例子概述。这个过程需要5分钟以上的时间来处理500条记录，基本上在750条记录后挂起。所以运行时间基本上是指数级增长的。

SQL语句

该过程的一般概述由两个选择块组成，这两个块从两个不同的源中选择数据。这些块被 Package 在一个较大的选择语句中，该语句筛选和匹配记录并选择其余的记录：
例如：

SELECT DISINCT
  *matched sales*
FROM
  (SELECT 
    *direct sales info from db1*
  FROM
    DB1
  WHERE
    sales_code = 'DIRECT') a
  
  db2.prod,
  db2.cont,
  db2.cust, --etc
  
  (SELECT *qualified customer information
  FROM *a few DB2 tables*
  WHERE code = 'DIR') qual
  --A few more of the above inline views to get eligible cust and price

WHERE
  *DB2 product numbers, customer numbers and contract numbers are matched to eachother & above 
  views* This is where the most time is being taken up.
  --ex
  cust.cont_num = cont.cont_num
  *DB1 records matched to DB2 records*
  --ex 
  a.cont_num = cont.cont_num

问题

这里我的问题是DB2块的性能，选择所有必要的不同表，创建内联视图并将它们匹配在一起，这本身就需要10分钟以上。
作为一个新手，我该如何调整它呢？使用临时表来存储这个块是否可以工作，这样就不必一遍又一遍地做它？或者我应该使用更多的内联视图吗？嵌套另一个像第一个选择块一样的选择块吗？

解释计划

OPERATION   OBJECT_NAME     OPTIONS     CARDINALITY     COST 

 SELECT STATEMENT 
         639039097   31298 
        
 HASH JOIN 
         639039097   31298 
                
 INDEX 
 CARSNG.IE_PRODID_IDX_4      FAST FULL SCAN      9184    13 
                
 HASH JOIN 
         639039097   29585 
                        
 TABLE ACCESS 
 CARSNG.UOM      FULL    6   3 
                        
 HASH JOIN 
         639039097   27881 
                                
 VIEW 
 CARSNG.index$_join$_011         8236    77 
                                        
 HASH JOIN 
               
                                                
 HASH JOIN 
               
                                                        
 INDEX 
 CARSNG.FK_PROD_IDX_4    FAST FULL SCAN      8236    20 
                                                        
 INDEX 
 CARSNG.IE_PROD_IDX_1    FAST FULL SCAN      8236    33 
                                                
 INDEX 
 CARSNG.PK_PROD      FAST FULL SCAN      8236    24 
                                
 HASH JOIN 
         639094333   26104 
                                        
 INDEX 
 CARSNG.IE_CPPT_IDX_3    FAST FULL SCAN      1254629     2473 
                                        
 NESTED LOOPS 
         634106      17709 
                                                
 HASH JOIN 
         2580    2212 
                                                        
 VIEW 
 CARSNG.index$_join$_014         24      2 
                                                                
 HASH JOIN 
               
                                                                        
 INDEX 
 CARSNG.AK_WHOAMI_IDX_1      FAST FULL SCAN      24      1 
                                                                        
 INDEX 
 CARSNG.PK_WHOAMI    FAST FULL SCAN      24      1 
                                                        
 HASH JOIN 
         2580    2210 
                                                                
 HASH JOIN 
         2589    2161 
                                                                        
 VIEW 
         2589    1690 
                                                                                
 HASH 
     GROUP BY    2589    1690 
                                                                                        
 NESTED LOOPS 
         2589    1689 
                                                                                                
 NESTED LOOPS 
         5874    1689 
                                                                                                        
 VIEW 
 SYS.VW_GBF_18       89      626 
                                                                                                                
 HASH 
     GROUP BY    89      626 
                                                                                                                        
 HASH JOIN 
     SEMI    1963    625 
                                                                                                                                
 TABLE ACCESS 
 CARSNG.CPGRP    FULL    1970    591 
                                                                                                                                
 VIEW 
 CARSNG.index$_join$_003         6415    34 
                                                                                                                                        
 HASH JOIN 
               
                                                                                                                                                
 INDEX 
 CARSNG.FK_CONT_IDX_3    FAST FULL SCAN      6415    18 
                                                                                                                                                
 INDEX 
 CARSNG.AK_CONT_IDX_1    FAST FULL SCAN      6415    25 
                                                                                                        
 INDEX 
 CARSNG.IE_CPPT_IDX_2    RANGE SCAN      66      2 
                                                                                                
 TABLE ACCESS 
 CARSNG.CPPT     BY INDEX ROWID      29      12 
                                                                        
 VIEW 
 CARSNG.index$_join$_013         43365   471 
                                                                                
 HASH JOIN 
               
                                                                                        
 HASH JOIN 
               
                                                                                                
 INDEX 
 CARSNG.PK_CPGRP     FAST FULL SCAN      43365   114 
                                                                                                
 INDEX 
 CARSNG.AK_CPGRP_IDX_4   FAST FULL SCAN      43365   192 
                                                                                        
 INDEX 
 CARSNG.IE_CPGRP_IDX_3   FAST FULL SCAN      43365   168 
                                                                
 VIEW 
 CARSNG.index$_join$_012         6415    49 
                                                                        
 HASH JOIN 
               
                                                                                
 INDEX 
 CARSNG.FK_CONT_IDX_3    FAST FULL SCAN      6415    18 
                                                                                
 INDEX 
 CARSNG.AK_CONT_IDX_3    FAST FULL SCAN      6415    44 
                                                
 INDEX 
 CARSNG.IE_ELIG_IDX_1    RANGE SCAN      246     6