ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用临时表来优化的存储过程

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

原创 Linux操作系统 作者:cc59 时间:2008-01-16 21:13:59 0 删除 编辑

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

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

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"是不可能存在的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/104152/viewspace-148371/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    132
  • 访问量
    288304