使用临时表来优化的存储过程

对于海量数据作检索的时候，通常会由于巨大的数据量，造成大量的排序和过滤。

SELECT TECHNOLOGY_RRN,
       TECHNOLOGY_ID,
       TECHNOLOGY_NAME,
       PART_RRN,
       PART_ID,
       LOT_TYPE_RRN,
       LOT_TYPE,
       STAGE_RRN,
       STAGE_ID,
       STAGE_ORDER,
       LOT_QTY AS LOT_BEGIN_QTY,
       MOVE_TARGET,
       WIP_TARGET,
       WIP_TARGET_BY_TECH,
       WIP_TARGET_BY_LOTTYPE
  FROM lot_wip_stage_day
 WHERE day_rrn = cnumDayRRN
   AND lot_qty <> 0
   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
       (SELECT s.FACILITY_RRN,
               s.TECHNOLOGY_ID,
               s.Part_ID,
               s.LOT_TYPE,
               s.STAGE_ID
          FROM lot_snapshot_summ s
         WHERE s.day_time = cdateMaxDayTime
           AND s.lot_status_category NOT IN
               ('SCHEDULE', 'COMPLETE', 'FINISH')
           AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
           AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
           AND s.stage_id is not null
           AND s.facility_rrn = InFaciRRN
         GROUP BY s.FACILITY_RRN,
                  s.TECHNOLOGY_ID,
                  s.Part_ID,
                  s.LOT_TYPE,
                  s.STAGE_ID);

其实类似于这样的sql语句，由于两表之间并不存在依赖关系，因此很难去从sql的写法上来优化，当然，这两个结果集之间分别来执行速度还是非常快的，但是一做not in操作，或者minus操作，就会慢100倍以上的速度，如果这时候，把两个结果集作为两个表
来处理，那么速度又是不一样的，这时候我们可以选择使用临时表把结果集存到临时表之后再作join操作，
先来看看之前的执行计划：

SQL> SELECT TECHNOLOGY_RRN,
  2         TECHNOLOGY_ID,
  3         TECHNOLOGY_NAME,
  4         PART_RRN,
  5         PART_ID,
  6         LOT_TYPE_RRN,
  7         LOT_TYPE,
  8         STAGE_RRN,
  9         STAGE_ID,
 10         STAGE_ORDER,
 11         LOT_QTY AS LOT_BEGIN_QTY,
 12         MOVE_TARGET,
 13         WIP_TARGET,
 14         WIP_TARGET_BY_TECH,
 15         WIP_TARGET_BY_LOTTYPE
 16    FROM lot_wip_stage_day
 17   WHERE day_rrn = 11222
 18     AND lot_qty <> 0
 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
 20         (SELECT s.FACILITY_RRN,
 21                 s.TECHNOLOGY_ID,
 22                 s.Part_ID,
 23                 s.LOT_TYPE,
 24                 s.STAGE_ID
 25            FROM lot_snapshot_summ s
 26           WHERE s.day_time = to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')
 27             AND s.lot_status_category NOT IN
 28                 ('SCHEDULE', 'COMPLETE', 'FINISH')
 29             AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
 30             AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
 31             AND s.stage_id is not null
 32             AND s.facility_rrn = 1
 33           GROUP BY s.FACILITY_RRN,
 34                    s.TECHNOLOGY_ID,
 35                    s.Part_ID,
 36                    s.LOT_TYPE,
 37                    s.STAGE_ID);

431 rows selected.

Elapsed: 00:032:43.60

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=
          15594)

   1    0   FILTER
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_D
          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
          rd=138)

   4    1     FILTER
   5    4       SORT (GROUP BY) (Cost=10 Card=1 Bytes=100)
   6    5         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_SNAPSHO
          T_SUMM' (Cost=3 Card=1 Bytes=100)

   7    6           INDEX (RANGE SCAN) OF 'PK_LOTSNS' (UNIQUE) (Cost=2
           Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   15644227  consistent gets
       1201  physical reads
          0  redo size
      54272  bytes sent via SQL*Net to client
       5225  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
        525  sorts (memory)
          0  sorts (disk)
        431  rows processed

创建临时表，这种临时表是在session 结果以后oracle自己清除数据，关于更多的临时表信息可以参数oracle doc:

CREATE GLOBAL TEMPORARY TABLE temp_result
        (facility_rrn number(15),
         TECHNOLOGY_ID varchar2(32),
         part_id varchar2(32),
         lot_type varchar2(32),
         stage_id varchar2(32)
         )
      ON COMMIT PRESERVE ROWS ;

然后在存储过程中加上以下部分：
 insert into temp_result
   SELECT s.FACILITY_RRN,
          s.TECHNOLOGY_ID,
          s.Part_ID,
          s.LOT_TYPE,
          s.STAGE_ID
     FROM lot_snapshot_summ s
    WHERE s.day_time =
          to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')
      AND s.lot_status_category NOT IN ('SCHEDULE', 'COMPLETE', 'FINISH')
      AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
      AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
      AND s.stage_id is not null
      AND s.facility_rrn = 1
    GROUP BY s.FACILITY_RRN,
             s.TECHNOLOGY_ID,
             s.Part_ID,
             s.LOT_TYPE,
             s.STAGE_ID

并将文中开始提到的sql语句改为：

SELECT TECHNOLOGY_RRN,
       TECHNOLOGY_ID,
       TECHNOLOGY_NAME,
       PART_RRN,
       PART_ID,
       LOT_TYPE_RRN,
       LOT_TYPE,
       STAGE_RRN,
       STAGE_ID,
       STAGE_ORDER,
       LOT_QTY AS LOT_BEGIN_QTY,
       MOVE_TARGET,
       WIP_TARGET,
       WIP_TARGET_BY_TECH,
       WIP_TARGET_BY_LOTTYPE
  FROM lot_wip_stage_day
 WHERE day_rrn = 11222
   AND lot_qty <> 0
   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
       (select * from temp_result);

然后再来看这个执行计划：

SQL> SELECT TECHNOLOGY_RRN,
  2         TECHNOLOGY_ID,
  3         TECHNOLOGY_NAME,
  4         PART_RRN,
  5         PART_ID,
  6         LOT_TYPE_RRN,
  7         LOT_TYPE,
  8         STAGE_RRN,
  9         STAGE_ID,
 10         STAGE_ORDER,
 11         LOT_QTY AS LOT_BEGIN_QTY,
 12         MOVE_TARGET,
 13         WIP_TARGET,
 14         WIP_TARGET_BY_TECH,
 15         WIP_TARGET_BY_LOTTYPE
 16    FROM lot_wip_stage_day
 17   WHERE day_rrn = 11222
 18     AND lot_qty <> 0
 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
 20         (select * from temp_result);

431 rows selected.

Elapsed: 00:00:00.48

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=
          15594)

   1    0   FILTER
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_D
          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
          rd=138)

   4    1     TABLE ACCESS (FULL) OF 'TEMP_RESULT' (Cost=16 Card=1 Byt
          es=85)

Statistics
----------------------------------------------------------
          0  recursive calls
       2084  db block gets
       3128  consistent gets
          0  physical reads
          0  redo size
      54272  bytes sent via SQL*Net to client
       5225  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        431  rows processed

SQL>

对比前后，优化前的逻辑读:15644227,执行时间为00:32:43.60
优化后：3128，时间：00:00:00.48

当然，并不是说所有情况都适合使用临时表，有时候可能使用array更加合适，
具体情况具体对待。"fast=true"是不可能存在的。