[原创]物化查询表，查询重写

8X|a
Z$i0D5zT0前几天看到有人问怎么看查询重写！当时有点忙，今天抽时间写一个脚本

一：首先，创建物化查询表
1G-]9m+@V2|0C:\>db2 connect to churndb user testITPUB个人空间)nIS5N bH q
输入 test 的当前密码：

   数据库连接信息

 数据库服务器         = DB2/NT 8.2.0
!V?(|`1Y
V"rT0 SQL 授权标识         = TEST
c,nm!M\0 本地数据库别名       = CHURNDB

"BgJ9pqa&[0C:\>db2 drop table churn_materialized
)D3V2Pbqw'@0DB20000I  SQL 命令成功完成。

C:\>db2 create table churn_materialized  as(  \ITPUB个人空间0`.R~%b9b4z,M*e
db2 (cont.) => select CUST_ID,ACC_NBR, CUST_AGE, CUST_SEX, REGION_COD \ITPUB个人空间g e|1MDBlM
db2 (cont.) =>     , PROD_TYPE, USER_TYPE, LEAVE_DATE, NOW_TALK_S, NOW_USE_S, STITPUB个人空间.aE,tNZST1SV|
RAT_G, NOW_REAL_S, \ITPUB个人空间+B ~Vy X;bry6x
db2 (cont.) =>     LOAD_TIME, NEXT_M1_REAL_S, AREA_CODE \
:A$c)h3_4ve&m!Y0db2 (cont.) =>   from TEST.CUST_CHURN_INFO_200710 where now_talk_s = '正常' \
:Wl'ez-P&a0db2 (cont.) =>   ) data initially deferred refresh immediate
6pw+JMLK0DB20000I  SQL 命令成功完成。

C:\>db2  refresh  table churn_materializedITPUB个人空间
H*S!lW {M
DB20000I  SQL 命令成功完成。

C:\>db2 runstats on table test.churn_materialized
{(Uf*VL-X] fL+l,O0DB20000I  RUNSTATS 命令成功完成。

$N`kd4ka4`"Q*|,o0二：ss.sql 如下：
:jN~ewkn@v%?0select CUST_ID,ACC_NBR, CUST_AGE, CUST_SEX from TEST.CUST_CHURN_INFO_200710 where now_talk_s = '正常';

三：因为sql查询的是TEST.CUST_CHURN_INFO_200710，如果系统能够走查询重写的话，
,q2V/d9Ul+J2q/J0那么查询结果应该是走test.churn_materialized，且先得到执行计划ITPUB个人空间,a u[1EiA.H-N
C:\>db2 set current explain mode explain
7pEfx yxh7s0DB20000I  SQL 命令成功完成。

C:\>db2 -tvf c:\ss.sqlITPUB个人空间Fc0D1\u9vS/[%]d
select CUST_ID,ACC_NBR, CUST_AGE from TEST.CUST_CHURN_INFO_200710 where now_talk
'C9CeD|W0_s = '正常'
J5h"Zb#Y$jb0SQL0217W  未执行该语句，因为仅在处理 Explain 信息请求。  SQLSTATE=01604

-V"{*V `:f,`E0C:\>db2 set current explain mode noITPUB个人空间
d!X8N1np K im
DB20000I  SQL 命令成功完成。

C:\>Db2exfmt -d churndb -g TIC -w -1 -n % -s % -# 0 -o c:\ZXT1.TXT
+V-f6f2W*~A"tA&ub0DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
/V)V1jhWTg0Licensed Material - Program Property of IBMITPUB个人空间
j+zu2b4R_
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.ITPUB个人空间b,@|E&n"_5cD
Connect to Database Successful.ITPUB个人空间5pllrbZ.p
Using only explain schema found: TEST    .
%~Z0r4|~/a4Ahm0Output is in c:\ZXT1.TXT.ITPUB个人空间\ f v)x
h+`1f
Executing Connect Reset -- Connect Reset was Successful.

C:\>db2 alter table test.churn_materialized drop materialized query
;U[P"\g4T^q#U0DB20000I  SQL 命令成功完成。

C:\>db2  drop  table churn_materialized
E;~OQjaNC0DB20000I  SQL 命令成功完成。

四：看执行计划

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
S@{|3T2aG;z0s0Licensed Material - Program Property of IBM
w'U9~7[ H Q;HR0IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION:   08.02.0
0d hzF+e_)q0SOURCE_NAME:   SQLC2E06ITPUB个人空间J KwIYX-@R
SOURCE_SCHEMA:   NULLID 
HD.K1Z9zt}[2w0SOURCE_VERSION:  ITPUB个人空间"x.fT2I0J2s
EXPLAIN_TIME:   2008-08-22-14.32.21.218001ITPUB个人空间&dI)E7THM-i5]
EXPLAIN_REQUESTER:  TEST   

Database Context:
7K^+n{)diF7K\0----------------
%a` W3kM-cd0 Parallelism:   Intra-Partition Parallelism
)k*y$~(IkcF+VT0 CPU Speed:   2.676618e-007ITPUB个人空间%j)m-o9`XE
 Comm Speed:   100
e k)c1][1]0 Buffer Pool size:  84222
L;H\AOv!O @SR0 Sort Heap size:  377ITPUB个人空间&L3X+B
r(x&x
 Database Heap size:  600ITPUB个人空间K
}U"ss!m+`
 Lock List size:  1000
(MzS uUa$k8?0 Maximum Lock List:  20ITPUB个人空间 u
Fh#{.G
 Average Applications:  1ITPUB个人空间K0uk&[.`
 Locks Available:  20400

Package Context:ITPUB个人空间y&J#U$f]{|
---------------
E.Ris1u*`%X0 SQL Type:   Dynamic
$Ms
[1Qj%?@8O0 Optimization Level:  5ITPUB个人空间
\~eMEn(V
 Blocking:   Block All Cursors
UP6qx8[0N9~].Ot0 Isolation Level:  Cursor Stability

---------------- STATEMENT 1  SECTION 201 ----------------ITPUB个人空间3E;Z*StE%X3MN
 QUERYNO:   1
f'~!I"Ow%c0 QUERYTAG:   CLP                
gzCw6g,|h-P0 Statement Type:  SelectITPUB个人空间G;}G:u$Da-j*Dg
 Updatable:   No
l j3U*n'|'vB+kT0 Deletable:   NoITPUB个人空间0`%}*i8E [[8n2y%LDk
 Query Degree:   -1

Original Statement:
2M:\4X])Ldg&jb5^0------------------ITPUB个人空间4~c5tXm2Z~
select CUST_ID,ACC_NBR, CUST_AGE
T5i i"O H0from TEST.CUST_CHURN_INFO_200710
$P,P D.dMB*ck+y0where now_talk_s = '....'

ITPUB个人空间5J:~`vf/m0y.o.N3qF
Optimized Statement:
P@'ueMYnCn3A0-------------------
/O6c,K$_e;i ?'Nu0SELECT Q1."CUST_ID" AS "CUST_ID", Q1."ACC_NBR" AS "ACC_NBR", Q1."CUST_AGE" AS
,M2je ID&uQ0        "CUST_AGE"ITPUB个人空间2Ut0R/n'w!_+M
FROM TEST.CUST_CHURN_INFO_200710 AS Q1
]x?&W7pM'V0WHERE (Q1."NOW_TALK_S" = '....')

Access Plan:ITPUB个人空间D:zrV~(LI~G$n
-----------ITPUB个人空间o)_;VWH#AL(S
 Total Cost:   1689.99
/`%coR4B7[ C-`0 Query Degree:  2

       Rows
2y[.Qa,~w:{a5F0      RETURN

rq9Gb Xk
~} b0      (   1)ITPUB个人空间%u{juJ%PL
       CostITPUB个人空间&q9`e x%A^0G
        I/O
,J;z#mB[!w0        |ITPUB个人空间Jbo)yL]8r
       59805ITPUB个人空间 zdH ]X4w
      LTQ   
V6X*?"pG:@0      (   2)ITPUB个人空间Hoy5eeF
      1689.99
&_R/y/I4?-q c?C0       1129
R PhR1} c8N+i,R0        |ITPUB个人空间2a;Y-n;OK7Df|/D
       59805
d%jTJR!e9l6kQ0      TBSCANITPUB个人空间b@6QfL#V,R
      (   3)
/^w6\}!I0      1678.5
J;M^$X gP
MK1\0       1129
6rhQ;T:U&{"_0        |
|+|@8LNcrRT0       59805ITPUB个人空间Di@9@ o3^'q
  TABLE: TEST    ITPUB个人空间5I4d^i,e&@"`v
 CHURN_MATERIALIZ

Y6q2H]G0 1) RETURN: (Return Result)
oy z4m:XyU0  Cumulative Total Cost:   1689.99
wQF0qu a:U Gf7`K3e0  Cumulative CPU Cost:   1.52911e+008
LT8Ck4Xy0  Cumulative I/O Cost:   1129
?Fw*LYc0  Cumulative Re-Total Cost:  27.5654
{@!B&n5{J3p0  Cumulative Re-CPU Cost:  1.02986e+008
Au\1M*yV9A0  Cumulative Re-I/O Cost:  0ITPUB个人空间 X%QDLijte,z
  Cumulative First Row Cost:  10.6762
(a%]&Vvg@d0  Estimated Bufferpool Buffers:  1129

  Arguments:ITPUB个人空间1h*p.kdS6v Lg#Er
  ---------ITPUB个人空间l2MG:gb,{
  BLDLEVEL: (Build level)ITPUB个人空间~ y pzeo
   DB2 v8.1.7.445 : s040812
{"a*\p8f|BTv0  STMTHEAP: (Statement heap size)ITPUB个人空间{E1_#l`
   2048

  Input Streams:
3K7L%r@I0  -------------
,i0LROE`1AH h0   3) From Operator #2

    Estimated number of rows:  59805ITPUB个人空间^J:`8|F|.ZD x
    Number of columns:   3
v#_JNu6B0    Subquery predicate ID:   Not Applicable

    Column Names:ITPUB个人空间r;I6n'I c;ApL
    ------------
kD-_0p xY(z
V0    +Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"

7p5q0jN uUnTh0 2) TQ    : (Table Queue)ITPUB个人空间|_9ld:OvUG OA
  Cumulative Total Cost:   1689.99ITPUB个人空间`MS
v)x+a|c
  Cumulative CPU Cost:   1.52911e+008ITPUB个人空间0k8MJYe%a}M
  Cumulative I/O Cost:   1129
AX&uWJ\)s4t0  Cumulative Re-Total Cost:  27.5654
FH y_3o?)ZJ0  Cumulative Re-CPU Cost:  1.02986e+008ITPUB个人空间[:{6y GA%b6w~"o
  Cumulative Re-I/O Cost:  0ITPUB个人空间K$l&M\Y
  Cumulative First Row Cost:  10.6762
o;ku7Qa+s:[5Z0  Estimated Bufferpool Buffers:  1129

  Arguments:
7E.I6XbX-M0  ---------
B1n1x0]2n.?0  LISTENER: (Listener Table Queue type)
@ O.]5Z u#bq1L0   FALSE
`^ baL3o Un|$G {h0  TQ TYPE : (Table queue type)ITPUB个人空间f{0VAhwPx
   LOCALITPUB个人空间bI*w dP&T i
  TQDEGREE: (Degree of Intra-Partition parallelism)ITPUB个人空间5ah-r F5~ X}0Rm
   2
9PRMT'nY0  TQMERGE : (Merging Table Queue flag)
L#kz*Mhuh0   FALSE
6RMk:C;^2N(G*h rF6_0  TQREAD  : (Table Queue Read type)ITPUB个人空间+o0^gRZ
S\
   READ AHEAD
Mx8T&J+M+{D
Qi0  UNIQUE  : (Uniqueness required flag)
L LSd~gq O+F0   FALSE

  Input Streams:
)DbJ rJ"Sk6a0  -------------
x O'NZc0   2) From Operator #3

    Estimated number of rows:  59805
W#]%H"Gi1k-kn0    Number of columns:   3ITPUB个人空间]5b?!OS3N'[1fA,j
    Subquery predicate ID:   Not Applicable

    Column Names:ITPUB个人空间^E%ew]:u
    ------------ITPUB个人空间)X$D8eE"P
    +Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"

ITPUB个人空间%MC$b;GSD1f
  Output Streams:ITPUB个人空间&O2}h#I9C y,uY
  --------------
)aa$oF{-XO5Dc"v0   3) To Operator #1

    Estimated number of rows:  59805ITPUB个人空间)vh-o P_|Rv
    Number of columns:   3ITPUB个人空间1?9qZ$Y!EE
    Subquery predicate ID:   Not Applicable

    Column Names:
*e} F)p;C["N0    ------------
8nRiB~,^&P0    +Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"

$_eotx}(P0 3) TBSCAN: (Table Scan)ITPUB个人空间a A)j2Pn,p
  Cumulative Total Cost:   1678.5ITPUB个人空间P%S+NN[
  Cumulative CPU Cost:   1.10006e+008
uc"D3Q1j?f1dZ0  Cumulative I/O Cost:   1129
t}+tu1tF'|K;Q0  Cumulative Re-Total Cost:  27.5654ITPUB个人空间g5s6s!K
iB)v)a
  Cumulative Re-CPU Cost:  1.02986e+008
2GuT(u%x/yn:T0  Cumulative Re-I/O Cost:  0
$E(E o3c/X
x0  Cumulative First Row Cost:  10.654ITPUB个人空间v,M)}v(cI3DM R7I
  Estimated Bufferpool Buffers:  1129

  Arguments:
3O6n|'Mg4k"D*j0  ---------ITPUB个人空间2T}[2kL!G8q
  MAXPAGES: (Maximum pages for prefetch)
g.`I1Tv&V$A%G{r0   ALLITPUB个人空间].B/M.f)R+\ x
  PREFETCH: (Type of Prefetch)
r8G:\I$@;Q?0   SEQUENTIAL
9q!{6e e+?`f?'r0  ROWLOCK : (Row Lock intent)
z6tfH2d0   NEXT KEY SHARE
6?Izw+h*L {0  SCANDIR : (Scan Direction)
-`k5qjJ9z,Z0   FORWARD
^A){@]$A0  SCANGRAN: (Intra-Partition Parallelism Scan Granularity)
-}p~-Qs%d1o'x0   2ITPUB个人空间J4b!j!x/ug
  SCANTYPE: (Intra-Partition Parallelism Scan Type)ITPUB个人空间 lGc1o2~
   LOCAL PARALLEL
\ I xm3{R"kW
^i0  SCANUNIT: (Intra-Partition Parallelism Scan Unit)
*YFV u;f)O9e'`{0   PAGEITPUB个人空间'~/Ze'd2zzY#aG pp1c
  TABLOCK : (Table Lock intent)ITPUB个人空间d mK \_$ug2N3BE-K&_
   INTENT SHAREITPUB个人空间J[%XX/O
?
  TBISOLVL: (Table access Isolation Level)
Sb*aSec0   CURSOR STABILITY

  Input Streams:ITPUB个人空间N&]} U!Z$`~F
  -------------ITPUB个人空间KG+hx}|
   1) From Object TEST.CHURN_MATERIALIZED

    Estimated number of rows:  59805
j6[;}eFou0    Number of columns:   3ITPUB个人空间I ^H"G4F$j'jmj-@
    Subquery predicate ID:   Not Applicable

    Column Names:ITPUB个人空间7Ht!EY:Of
    ------------ITPUB个人空间_zOBO9zg1FkC
    +Q1."CUST_AGE"+Q1."ACC_NBR"+Q1."CUST_ID"

B+|3|)Y1o8yH0  Output Streams:
g4iUj L"Ku.e+c!O3Q(s0  --------------ITPUB个人空间^ r5a8H%i M*J
   2) To Operator #2

    Estimated number of rows:  59805
/ri6uJV|"@%eI0    Number of columns:   3
#bw#Le8s+Jv7Yo0    Subquery predicate ID:   Not Applicable

    Column Names:
j5_5nI$hR0    ------------ITPUB个人空间 n(f:^_-d [ ?*f
    +Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"

ITPUB个人空间SnEZDI w
Objects Used in Access Plan:
,n/iN!|O-pYf0---------------------------

 Schema: TEST   
@ j }!y^([*w]0 Name:  CUST_CHURN_INFO_200710ITPUB个人空间\5B q*a8Y7[2U M2K
 Type:  Table (reference only)

 Schema: TEST   
-T*N2{R6IT?w0 Name:  CHURN_MATERIALIZEDITPUB个人空间.E7rX9b0V,W1L#xHG
 Type:  Table
m-]Y6u,N5K;y+G|0   Time of creation:   2008-08-22-14.31.47.546001ITPUB个人空间K5O[.|1QZP}
   Last statistics update:  2008-08-22-14.31.50.625000ITPUB个人空间R g3A$U5}
   Number of columns:   15
(y^WU|br;CS1c0   Number of rows:   59805ITPUB个人空间PX6]q0Qe*e
U)J
   Width of rows:    148
4W4M%Gv&u0   Number of buffer pool pages:  1129
'B*~!R8t R \0   Distinct row values:   NoITPUB个人空间}@a#F9vSIo
   Tablespace name:   USERSPACE1       ITPUB个人空间iz@L;y p4H
   Tablespace overhead:   10.500000ITPUB个人空间#hl hF&m;Z$U{#j2Q
   Tablespace transfer rate:  0.140000ITPUB个人空间DS3y%Z"d
   Source for statistics:   Single Node
ZXu_+J(B|)y0   Prefetch page count:   16
] s}
r3?+F5t0   Container extent page count:  16
1xj_$Q7B,o0   Table overflow record count:  0
!f H,T`M0   Table Active Blocks:   -1