|
|
前几天看到有人问怎么看查询重写！当时有点忙，今天抽时间写一个脚本
一：首先，创建物化查询表
[php]
C:\>db2 connect to churndb user test
输入 test 的当前密码：
数据库连接信息
数据库服务器 = DB2/NT 8.2.0
SQL 授权标识 = TEST
本地数据库别名 = CHURNDB
C:\>db2 drop table churn_materialized
DB20000I SQL 命令成功完成。
C:\>db2 create table churn_materialized as( \
db2 (cont.) => select CUST_ID,ACC_NBR, CUST_AGE, CUST_SEX, REGION_COD \
db2 (cont.) => , PROD_TYPE, USER_TYPE, LEAVE_DATE, NOW_TALK_S, NOW_USE_S, ST
RAT_G, NOW_REAL_S, \
db2 (cont.) => LOAD_TIME, NEXT_M1_REAL_S, AREA_CODE \
db2 (cont.) => from TEST.CUST_CHURN_INFO_200710 where now_talk_s = '正常' \
db2 (cont.) => ) data initially deferred refresh immediate
DB20000I SQL 命令成功完成。
C:\>db2 refresh table churn_materialized
DB20000I SQL 命令成功完成。
C:\>db2 runstats on table test.churn_materialized
DB20000I RUNSTATS 命令成功完成。
[php]
二：ss.sql 如下：
select CUST_ID,ACC_NBR, CUST_AGE, CUST_SEX from TEST.CUST_CHURN_INFO_200710 where now_talk_s = '正常';
三：因为sql查询的是TEST.CUST_CHURN_INFO_200710，如果系统能够走查询重写的话，
那么查询结果应该是走test.churn_materialized，且先得到执行计划
[/php]
C:\>db2 set current explain mode explain
DB20000I SQL 命令成功完成。
C:\>db2 -tvf c:\ss.sql
select CUST_ID,ACC_NBR, CUST_AGE from TEST.CUST_CHURN_INFO_200710 where now_talk
_s = '正常'
SQL0217W 未执行该语句，因为仅在处理 Explain 信息请求。 SQLSTATE=01604
C:\>db2 set current explain mode no
DB20000I SQL 命令成功完成。
C:\>Db2exfmt -d churndb -g TIC -w -1 -n % -s % -# 0 -o c:\ZXT1.TXT
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
Using only explain schema found: TEST .
Output is in c:\ZXT1.TXT.
Executing Connect Reset -- Connect Reset was Successful.
C:\>db2 alter table test.churn_materialized drop materialized query
DB20000I SQL 命令成功完成。
C:\>db2 drop table churn_materialized
DB20000I SQL 命令成功完成。
[/php]
四：看执行计划
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 08.02.0
SOURCE_NAME: SQLC2E06
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2008-08-22-14.32.21.218001
EXPLAIN_REQUESTER: TEST
Database Context:
----------------
Parallelism: Intra-Partition Parallelism
CPU Speed: 2.676618e-007
Comm Speed: 100
Buffer Pool size: 84222
Sort Heap size: 377
Database Heap size: 600
Lock List size: 1000
Maximum Lock List: 20
Average Applications: 1
Locks Available: 20400
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: -1
Original Statement:
------------------
select CUST_ID,ACC_NBR, CUST_AGE
from TEST.CUST_CHURN_INFO_200710
where now_talk_s = '....'
Optimized Statement:
-------------------
SELECT Q1."CUST_ID" AS "CUST_ID", Q1."ACC_NBR" AS "ACC_NBR", Q1."CUST_AGE" AS
"CUST_AGE"
FROM TEST.CUST_CHURN_INFO_200710 AS Q1
WHERE (Q1."NOW_TALK_S" = '....')
Access Plan:
-----------
Total Cost: 1689.99
Query Degree: 2
Rows
RETURN
( 1)
Cost
I/O
|
59805
LTQ
( 2)
1689.99
1129
|
59805
TBSCAN
( 3)
1678.5
1129
|
59805
TABLE: TEST
CHURN_MATERIALIZ
1) RETURN: (Return Result)
Cumulative Total Cost: 1689.99
Cumulative CPU Cost: 1.52911e+008
Cumulative I/O Cost: 1129
Cumulative Re-Total Cost: 27.5654
Cumulative Re-CPU Cost: 1.02986e+008
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 10.6762
Estimated Bufferpool Buffers: 1129
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.7.445 : s040812
STMTHEAP: (Statement heap size)
2048
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 59805
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"
2) TQ : (Table Queue)
Cumulative Total Cost: 1689.99
Cumulative CPU Cost: 1.52911e+008
Cumulative I/O Cost: 1129
Cumulative Re-Total Cost: 27.5654
Cumulative Re-CPU Cost: 1.02986e+008
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 10.6762
Estimated Bufferpool Buffers: 1129
Arguments:
---------
LISTENER: (Listener Table Queue type)
FALSE
TQ TYPE : (Table queue type)
LOCAL
TQDEGREE: (Degree of Intra-Partition parallelism)
2
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 59805
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"
Output Streams:
--------------
3) To Operator #1
Estimated number of rows: 59805
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 1678.5
Cumulative CPU Cost: 1.10006e+008
Cumulative I/O Cost: 1129
Cumulative Re-Total Cost: 27.5654
Cumulative Re-CPU Cost: 1.02986e+008
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 10.654
Estimated Bufferpool Buffers: 1129
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
SCANGRAN: (Intra-Partition Parallelism Scan Granularity)
2
SCANTYPE: (Intra-Partition Parallelism Scan Type)
LOCAL PARALLEL
SCANUNIT: (Intra-Partition Parallelism Scan Unit)
PAGE
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Input Streams:
-------------
1) From Object TEST.CHURN_MATERIALIZED
Estimated number of rows: 59805
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1."CUST_AGE"+Q1."ACC_NBR"+Q1."CUST_ID"
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 59805
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CUST_AGE"+Q2."ACC_NBR"+Q2."CUST_ID"
Objects Used in Access Plan:
---------------------------
Schema: TEST
Name: CUST_CHURN_INFO_200710
Type: Table (reference only)
Schema: TEST
Name: CHURN_MATERIALIZED
Type: Table
Time of creation: 2008-08-22-14.31.47.546001
Last statistics update: 2008-08-22-14.31.50.625000
Number of columns: 15
Number of rows: 59805
Width of rows: 148
Number of buffer pool pages: 1129
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 10.500000
Tablespace transfer rate: 0.140000
Source for statistics: Single Node
Prefetch page count: 16
Container extent page count: 16
Table overflow record count: 0
Table Active Blocks: -1
可以看到系统中使用到了查询重写：执行计划中没有直接查询表TEST.CUST_CHURN_INFO_200710，
而是查询表churn_materialized
[ 本帖最后由 myfriend2010 于 2008-8-22 14:47 编辑 ] |
|
IP卡
狗仔卡
发表于 2008-8-22 14:39
收藏
提升卡
置顶卡
变色卡
显身卡
楼主
发表于 2008-8-25 21:02