使用临时表来优化的存储过程
对于海量数据作检索的时候,通常会由于巨大的数据量,造成大量的排序和过滤。
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/104152/viewspace-148371/