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

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

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

SELECT TECHNOLOGY_RRN,
R[ TZ"}y\104152       TECHNOLOGY_ID,
u$t[l2q M104152       TECHNOLOGY_NAME,
2xm3| O#P.uD104152       PART_RRN,
:BE3`4x*O/S104152       PART_ID,ITPUB个人空间 X7Ab\+h3h"x.F
       LOT_TYPE_RRN,ITPUB个人空间'D/C%zRy^v5m*e
       LOT_TYPE,
*P7VV0hlV#V7m
B104152       STAGE_RRN,
:gv3l/V0^r2dLh104152       STAGE_ID,ITPUB个人空间n0}0YA|M:{K(k3Q
       STAGE_ORDER,
8e;J`5g/T1C ehA104152       LOT_QTY AS LOT_BEGIN_QTY,
M&Jo(LvM2sP%y104152       MOVE_TARGET,
R,m4Uk0j-z*e,n104152       WIP_TARGET,ITPUB个人空间 KK7btt!|"m"}
       WIP_TARGET_BY_TECH,
ES.IfD:q104152       WIP_TARGET_BY_LOTTYPE

HzS*W6AD5q104152  FROM lot_wip_stage_dayITPUB个人空间B P5~?,@p8J
 WHERE day_rrn = cnumDayRRN
z {x#j{,g \Y&c#[104152   AND lot_qty <> 0
}6[+J:`l%?E104152   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT INITPUB个人空间 a$dJpsvf
       (SELECT s.FACILITY_RRN,
lgc,Y"q bo104152               s.TECHNOLOGY_ID,ITPUB个人空间 @/Dm.L\
               s.Part_ID,ITPUB个人空间9q2a;i2Y.Wnb,x%[
               s.LOT_TYPE,
'Q } ^9_7F oP-{104152               s.STAGE_ID
Gd1^u"kwV104152          FROM lot_snapshot_summ s
l#j^VDR}104152         WHERE s.day_time = cdateMaxDayTime
SfT o({E104152           AND s.lot_status_category NOT INITPUB个人空间 @BbO|b
               ('SCHEDULE', 'COMPLETE', 'FINISH')ITPUB个人空间yL/ChR
?s
           AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')ITPUB个人空间])RH9jZ&`
           AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
O%A.qY,i104152           AND s.stage_id is not nullITPUB个人空间x
U$Ed~9?
           AND s.facility_rrn = InFaciRRN
i_8XIA0xJ104152         GROUP BY s.FACILITY_RRN,
K0m6IOH)Y&Wl104152                  s.TECHNOLOGY_ID,
'uk%rl*m`C
x(L104152                  s.Part_ID,ITPUB个人空间Gk,W$w9Y
                  s.LOT_TYPE,ITPUB个人空间 E+c zZZ^)w_
                  s.STAGE_ID);

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

SQL> SELECT TECHNOLOGY_RRN,
jH'x_ ].p(@104152  2         TECHNOLOGY_ID,ITPUB个人空间]yZ:_}N7|Dn
  3         TECHNOLOGY_NAME,ITPUB个人空间,d Z:|Oh:B
  4         PART_RRN,
o[+H[~*I6zN104152  5         PART_ID,ITPUB个人空间c!pZS%z2Y3Q_a9X
  6         LOT_TYPE_RRN,
,S$Ip:]/PyWE:P*J,E104152  7         LOT_TYPE,
'wMh l@ D104152  8         STAGE_RRN,
X5jP`nb k({y104152  9         STAGE_ID,
m1O'}B`104152 10         STAGE_ORDER,
k+g.J({k0K,jz7r104152 11         LOT_QTY AS LOT_BEGIN_QTY,ITPUB个人空间#] EH
xm)~
 12         MOVE_TARGET,ITPUB个人空间3?e~'R%a^8A8H"m+v9A
 13         WIP_TARGET,
G7G8GXU*E2?&\104152 14         WIP_TARGET_BY_TECH,ITPUB个人空间B4d5Z(z!TGS
 15         WIP_TARGET_BY_LOTTYPE
@ V-nc4Y(x2e7|T M104152 16    FROM lot_wip_stage_day
`(GtL.]t104152 17   WHERE day_rrn = 11222ITPUB个人空间j7Hbl!NG"_aVK
 18     AND lot_qty <> 0ITPUB个人空间f4~ z%fcY ~
 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT INITPUB个人空间I NYt.{D
_#j
 20         (SELECT s.FACILITY_RRN,ITPUB个人空间9tyh:HV bG(d
 21                 s.TECHNOLOGY_ID,
@Lp[d!Yk104152 22                 s.Part_ID,ITPUB个人空间1xw nU.EP:a Ni
 23                 s.LOT_TYPE,ITPUB个人空间G2P,I9~3e
 24                 s.STAGE_IDITPUB个人空间} F!r}3pP8_ Q`({i
 25            FROM lot_snapshot_summ s
F;v
m;R/y3q([K104152 26           WHERE s.day_time = to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')
x%x
E?sr6?nG104152 27             AND s.lot_status_category NOT IN
0]F f B-?I"lXDs104152 28                 ('SCHEDULE', 'COMPLETE', 'FINISH')ITPUB个人空间1l7Z+J%W(L;k&Cg+d
 29             AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')ITPUB个人空间yyy.]-U#h6a3@
 30             AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
1t;T\TK^104152 31             AND s.stage_id is not null
c1M1B-lmpx+G104152 32             AND s.facility_rrn = 1
x6CL A`1ITB!cw104152 33           GROUP BY s.FACILITY_RRN,
;k(x1q&ECg104152 34                    s.TECHNOLOGY_ID,
T%jQnv`%kJ:i-s)w104152 35                    s.Part_ID,
$c/x0j)fN9v:Y3d"e$z+[w104152 36                    s.LOT_TYPE,
Q(c\%k} b&O104152 37                    s.STAGE_ID);

431 rows selected.

Elapsed: 00:032:43.60

Execution PlanITPUB个人空间j1E1c C1So9{
----------------------------------------------------------ITPUB个人空间2J0@"A
v`hoH
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=ITPUB个人空间0D{eJ"z O ?,L8S.SO
          15594)

   1    0   FILTER
)BJGuNP-i104152   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_DITPUB个人空间%Fh+@'f@'U.i
          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
7X/OS PaJq$T104152          rd=138)

   4    1     FILTER
?mApm8tOJ/B104152   5    4       SORT (GROUP BY) (Cost=10 Card=1 Bytes=100)ITPUB个人空间"@6a.J/Og
   6    5         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_SNAPSHO
REiaV G1U6}+{oc104152          T_SUMM' (Cost=3 Card=1 Bytes=100)

   7    6           INDEX (RANGE SCAN) OF 'PK_LOTSNS' (UNIQUE) (Cost=2ITPUB个人空间SV%E'{f;\FD
           Card=1)

Statistics
5u@cz s/o d?9`7@104152----------------------------------------------------------ITPUB个人空间t/V'Ul0N6L C
          0  recursive calls
Z4W uB4J(~*k pdO104152          0  db block gets
d.H.v8O5r*|104152   15644227  consistent gets
t r2H.Q2w8bC2k0U kAU104152       1201  physical reads
0t2A_&r&]104152          0  redo sizeITPUB个人空间WKsQ `7c
      54272  bytes sent via SQL*Net to clientITPUB个人空间 t`-]VXf6~
       5225  bytes received via SQL*Net from clientITPUB个人空间C[cN-P2S U#W
         30  SQL*Net roundtrips to/from clientITPUB个人空间T D$P8UNGH @
        525  sorts (memory)ITPUB个人空间W
M|@Yx
          0  sorts (disk)ITPUB个人空间\mAu4IO
        431  rows processed

y.L:] Qv(k6Ulf]
z~104152创建临时表，这种临时表是在session 结果以后oracle自己清除数据，关于更多的临时表信息可以参数oracle doc:

CREATE GLOBAL TEMPORARY TABLE temp_resultITPUB个人空间;d%Ckc)ID
        (facility_rrn number(15),ITPUB个人空间1k2ok&Ef
         TECHNOLOGY_ID varchar2(32),ITPUB个人空间(].MNe's
         part_id varchar2(32),
Fk(rh'w/F104152         lot_type varchar2(32),
-v;gDh)d;j3BNmz104152         stage_id varchar2(32)
-x
AYf_)U)Fb104152         )ITPUB个人空间 F1] r`
zEgo.x#qR
      ON COMMIT PRESERVE ROWS ;

ITPUB个人空间!j M)[1H _6N
然后在存储过程中加上以下部分：
}(UPC&V$r'J`+RG104152 insert into temp_result
yS\`G!@b1k#Hf7`104152   SELECT s.FACILITY_RRN,
p z{&pZ~104152          s.TECHNOLOGY_ID,
g8t,U'a7G8V5B104152          s.Part_ID,ITPUB个人空间.XR"Mmcn
          s.LOT_TYPE,
@nWJ|`Cb104152          s.STAGE_IDITPUB个人空间 H~J'T Z9V_ g
     FROM lot_snapshot_summ s
t-}!F(yo Pkrc104152    WHERE s.day_time =
u!\n*G'E104152          to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')ITPUB个人空间iQvC"v;Z6S
      AND s.lot_status_category NOT IN ('SCHEDULE', 'COMPLETE', 'FINISH')
L)Vwfk
xl104152      AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
5Km
}9D0o;{3Q:^@104152      AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
P;|5X!kMfK"l2y104152      AND s.stage_id is not null
t9j9I2K:c.v104152      AND s.facility_rrn = 1ITPUB个人空间$rE-h8c;r?By
    GROUP BY s.FACILITY_RRN,
9mKy4TA;Un6W9`104152             s.TECHNOLOGY_ID,ITPUB个人空间*opMj'N
             s.Part_ID,ITPUB个人空间dua&NvP&G
             s.LOT_TYPE,
6y
wwA @ye104152             s.STAGE_ID

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

SELECT TECHNOLOGY_RRN,ITPUB个人空间 lge9aK-|8AXl
       TECHNOLOGY_ID,
ILSTa+x(F-^104152       TECHNOLOGY_NAME,
[_G-JgPS{$We104152       PART_RRN,ITPUB个人空间4r @v)Z6Wq/v
       PART_ID,ITPUB个人空间Hn)[G-s`)q$I e-`
       LOT_TYPE_RRN,ITPUB个人空间#^w b%q,Xr1yP!GR
       LOT_TYPE,
.D.r@(EAVr104152       STAGE_RRN,
!v!R{[_V5j6W#N104152       STAGE_ID,ITPUB个人空间5_,eC I"? DBo
       STAGE_ORDER,ITPUB个人空间`+}+U2L+jBa
       LOT_QTY AS LOT_BEGIN_QTY,ITPUB个人空间D2_'f/jf&HH3K
       MOVE_TARGET,
8s
h ZP|*H-I$R*Pv104152       WIP_TARGET,
b#Oi5J'}0\0T104152       WIP_TARGET_BY_TECH,ITPUB个人空间C![ {*Y]T'FM;SP
       WIP_TARGET_BY_LOTTYPEITPUB个人空间q^0q3]l"D)\h%u CO
  FROM lot_wip_stage_day
_
rtjaE104152 WHERE day_rrn = 11222
@%]_M r5wt104152   AND lot_qty <> 0
eIv+X
Lv104152   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT INITPUB个人空间%J,f4kFR%X
       (select * from temp_result);

[&Q+[,j#dlH#P104152然后再来看这个执行计划：

SQL> SELECT TECHNOLOGY_RRN,ITPUB个人空间k%|&E4^"H
  2         TECHNOLOGY_ID,ITPUB个人空间4u)Q;h#]1_1D%\+YC:['L
  3         TECHNOLOGY_NAME,
$I1L"MD%z104152  4         PART_RRN,ITPUB个人空间"i~VX;S6qye
  5         PART_ID,ITPUB个人空间yM?.y#yn
  6         LOT_TYPE_RRN,
6e J|0u j9C104152  7         LOT_TYPE,ITPUB个人空间mC:ci5gf``q(i
  8         STAGE_RRN,ITPUB个人空间%\zjW`9t G
  9         STAGE_ID,ITPUB个人空间+S@v6Q&^] {e
 10         STAGE_ORDER,ITPUB个人空间:r$P#v8k5i]@y
 11         LOT_QTY AS LOT_BEGIN_QTY,
9o(c4M*s8d+R104152 12         MOVE_TARGET,ITPUB个人空间7USWJo!h|f9h
 13         WIP_TARGET,ITPUB个人空间b!r%Do[
 14         WIP_TARGET_BY_TECH,
#]2`}RneE104152 15         WIP_TARGET_BY_LOTTYPE
yz$AQ!]3nvJ104152 16    FROM lot_wip_stage_dayITPUB个人空间Q\xU?by
 17   WHERE day_rrn = 11222
pv$~ggNK"ay0I104152 18     AND lot_qty <> 0
"vI5u;a3Y104152 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT INITPUB个人空间].@6v`+QQb!Jh
 20         (select * from temp_result);

431 rows selected.

Elapsed: 00:00:00.48

Execution Plan
|K gZq Q104152----------------------------------------------------------
A xwj7bkU*n%y104152   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=ITPUB个人空间h/r(~ {i7L;]&|
          15594)

   1    0   FILTERITPUB个人空间X ?,dw$_
B7v
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_D
s9Q8MW%YZ
m104152          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
7u6g}7H?5^104152          rd=138)

   4    1     TABLE ACCESS (FULL) OF 'TEMP_RESULT' (Cost=16 Card=1 Byt
.pl!p u`*b104152          es=85)

Statistics
l5xwkvc104152----------------------------------------------------------
R"_u*C6W1~104152          0  recursive callsITPUB个人空间3gW8Z`$V&st ~r
       2084  db block gets
9I.~I.@ I UA0Q104152       3128  consistent gets
|@a g0ck104152          0  physical readsITPUB个人空间 Y(L9T8gC ?iy}
          0  redo sizeITPUB个人空间Xt@%TED-c
      54272  bytes sent via SQL*Net to client
8V;O||x104152       5225  bytes received via SQL*Net from clientITPUB个人空间vj.vvr[3AU
         30  SQL*Net roundtrips to/from client
?xI(s P n7M%HP/n104152          2  sorts (memory)
]iTL}5E/]}U9i104152          0  sorts (disk)
c?
K@T104152        431  rows processed

SQL>

,Y.q'SXghl1| ?h104152对比前后，优化前的逻辑读:15644227,执行时间为00:32:43.60ITPUB个人空间SnumJ7Q;l
优化后：3128，时间：00:00:00.48

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