パーティショﾝ SELECT * FROM CUSTLIST
WHERE SUBSDATE BETWEEN ‘1/1/2000’ AND ’12/31/2000’
1991 1992
1996 1997 1998
1999 2000 2001 1993
1994 1995
CREATE TABLE custlist(subsdate DATE, Province CHAR(2), AccountID INT) PARTITION BY RANGE(subsdate) (STARTING FROM ‘1/1/1990’ IN ts1, STARTING FROM ‘1/1/1991’ IN ts1, STARTING FROM ‘1/1/1992’ IN ts1, STARTING FROM ‘1/1/1993’ IN ts2, STARTING FROM ‘1/1/1994’ IN ts2, STARTING FROM ‘1/1/1995’ IN ts2, STARTING FROM ‘1/1/1996’ IN ts3, STARTING FROM ‘1/1/1997’ IN ts3, STARTING FROM ‘1/1/1998’ IN ts3, STARTING FROM ‘1/1/1999’ IN ts4, STARTING FROM ‘1/1/2000’ IN ts4, STARTING FROM ‘1/1/2001’
ENDING ’12/31/2001’ IN ts4)
db2explnの出力（表スキャン）(抜粋)
Access Table Name = V9ICHI.CUSTLIST ID = -6,-32768
| #Columns = 3
| Data-Partitioned Table
| Data Partition Elimination Info:
| | Range 1:
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: 2000-01-01
| | | | Stop Key: Inclusive Value
| | | | | 1: 2000-12-31
| Active Data Partitions: 10
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 2
| | Return Data to Application
| | | #Columns = 3 Return Data Completion End of section
パーティション表の区分 限定スキャン情報 パーティション表では、
テーブルIDがマイナスとなる
Optimizer Plan:
RETURN ( 1)
| TBSCAN ( 2)
| Table:
V9ICHI CUSTLIST
スキャンした
パーティション番号
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 135
db2exfmtの出力(表スキャン) （抜粋）
Original Statement:
---select *
from custlist
where subsdate between '1/1/2000' and '12/31/2000'
Optimized Statement:
---SELECT Q1."SUBSDATE" AS "SUBSDATE", Q1."PROVINCE" AS "PROVINCE", Q1."ACCOUNTID" AS "ACCOUNTID"
FROM V9ICHI.CUSTLIST AS Q1
WHERE (Q1."SUBSDATE" <= '2000-12-31') AND ('2000-01-01' <= Q1."SUBSDATE")
Access Plan:
---Total Cost: 293.218
Query Degree: 1
Rows RETURN ( 1) Cost
I/O
| 18253.4 TBSCAN ( 2) 293.218
134
| 280200 DP-TABLE: V9ICHI
CUSTLIST
「DP-TABLE」とあるこ とからパーティション 表であることが分かる
db2exfmtの出力(表スキャン) （続き）
Plan Details:
---1) RETURN: (Return Result)
Cumulative Total Cost: 293.218 Cumulative CPU Cost: 4.21134e+07 Cumulative I/O Cost: 134 Cumulative Re-Total Cost: 52.6432 Cumulative Re-CPU Cost: 4.12781e+07 Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8737 Estimated Bufferpool Buffers: 134 Arguments:
---BLDLEVEL: (Build level) DB2 v9.1.0.0 : s060121 STMTHEAP: (Statement heap size)
4096
Input Streams:
---2) From Operator #2
Estimated number of rows: 18253.4
Number of columns: 3
Subquery predicate ID: Not Applicable Column Names:
---+Q2."ACCOUNTID"+Q2."PROVINCE"+Q2."SUBSDATE"
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 293.218 Cumulative CPU Cost: 4.21134e+07 Cumulative I/O Cost: 134 Cumulative Re-Total Cost: 52.6432 Cumulative Re-CPU Cost: 4.12781e+07 Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8737 Estimated Bufferpool Buffers: 134
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 137
db2exfmtの出力(表スキャン) （続き）
Arguments:
---DPESTFLG:
(Number of data partitions accessed are Estimated) FALSE
DPLSTPRT: (List of data partitions accessed) 10
DPNUMPRT: (Number of data partitions accessed) 1
GLOBLOCK: (Global Lock intent) INTENT SHARE MAXPAGES: (Maximum pages for prefetch)
ALL PREFETCH: (Type of Prefetch)
SEQUENTIAL ROWLOCK : (Row Lock intent)
NEXT KEY SHARE SCANDIR : (Scan Direction)
FORWARD TABLOCK : (Table Lock intent)
INTENT SHARE TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
---2) Sargable Predicate
Comparison Operator: Less Than or Equal (<=) Subquery Input Required: No
Filter Factor: 0.934715 Predicate Text:
---(Q1."SUBSDATE" <= '2000-12-31') 3) Sargable Predicate
Comparison Operator: Less Than or Equal (<=) Subquery Input Required: No
Filter Factor: 0.13043 Predicate Text:
---('2000-01-01' <= Q1."SUBSDATE")
db2exfmtの出力(表スキャン) （続き）
DP Elim Predicates:
---Range 1)
Stop Predicate: (Q1."SUBSDATE" <= '2000-12-31') Start Predicate: ('2000-01-01' <= Q1."SUBSDATE") Input Streams:
---1) From Object V9ICHI.CUSTLIST Estimated number of rows: 280200 Number of columns: 4
Subquery predicate ID: Not Applicable Column Names:
---+Q1.$RID$+Q1."ACCOUNTID"+Q1."PROVINCE"
+Q1."SUBSDATE"
Output Streams:
---2) To Operator #1
Estimated number of rows: 18253.4 Number of columns: 3
Subquery predicate ID: Not Applicable Column Names:
---+Q2."ACCOUNTID"+Q2."PROVINCE"+Q2."SUBSDATE"
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 139
db2exfmtの出力(表スキャン) （続き）
Objects Used in Access Plan:
---Schema: V9ICHI
Name: CUSTLIST
Type: Data Partitioned Table
Time of creation: 2006-03-24-17.40.51.000263 Last statistics update: 2006-03-24-17.47.16.699084
Number of columns: 3
Number of rows: 280200
Width of rows: 19
Number of buffer pool pages: 1608 Number of data partitions: 12
Distinct row values: No
Tablespace name: <VARIOUS>
Tablespace overhead: 12.670000 Tablespace transfer rate: 0.180000 Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
RCT pages: -1
RCT full key cardinality: -1 Index first key cardinality: -1 Index first 2 keys cardinality: -1 Index first 3 keys cardinality: -1 Index first 4 keys cardinality: -1
データ・パーティションの数
表スペースの名前は、
<VARIOUS>となる
区分限定スキャン実行例(索引スキャン)
1990 CUSTLIST
表
表スペース1 表スペース2 表スペース3 表スペース4 SELECT * FROM CUSTLIST
WHERE SUBSDATE BETWEEN ‘1/1/1996’ AND ’1/5/2000’ AND PROVINCE =‘1’
1991 1992
1996 1997 1998
1999 2000 2001 1993
1994 1995
CREATE TABLE custlist(subsdate DATE, Province CHAR(2), AccountID INT) PARTITION BY RANGE(subsdate) (STARTING FROM ‘1/1/1990’ IN ts1, STARTING FROM ‘1/1/1991’ IN ts1, STARTING FROM ‘1/1/1992’ IN ts1, STARTING FROM ‘1/1/1993’ IN ts2, STARTING FROM ‘1/1/1994’ IN ts2, STARTING FROM ‘1/1/1995’ IN ts2, STARTING FROM ‘1/1/1996’ IN ts3, STARTING FROM ‘1/1/1997’ IN ts3, STARTING FROM ‘1/1/1998’ IN ts3, STARTING FROM ‘1/1/1999’ IN ts4, STARTING FROM ‘1/1/2000’ IN ts4, STARTING FROM ‘1/1/2001’
ENDING ’12/31/2001’ IN ts4)
CREATE INDEX idx2 on custlist(province)
パーティションキー以外の 列に索引定義
Optimizer Plan:
RETURN ( 1)
| FETCH (----) / ¥ RIDSCN Table:
( 3) V9ICHI
| CUSTLIST SORT
( 4)
| IXSCAN ( 5) / ¥ Index: Table:
V9ICHI V9ICHI IDX2 CUSTLIST
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 141 Access Table Name = V9ICHI.CUSTLIST ID = -6,-32768
| Index Scan: Name = V9ICHI.IDX2 ID = 2
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: PROVINCE (Ascending)
| #Columns = 0
| Data-Partitioned Table
| Data Partition Elimination Info:
| | Range 1:
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: 1996-01-01
| | | | Stop Key: Inclusive Value
| | | | | 1: 2000-01-05
| Active Data Partitions: 6-10
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '1 '
| | Stop Key: Inclusive Value
| | | | 1: '1 '
| Index-Only Access
| Index Prefetch: Eligible 160
| Isolation Level:
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 23350.000000
| | | | Row Width = 16
| | | Piped
| | | Duplicate Elimination Sorted Temp Table Completion ID = t1 List Prefetch Preparation
| Access Table Name = V9ICHI.CUSTLIST ID = -6,-32768
| | #Columns = 2
| | Data-Partitioned Table
| | All data partitions will be accessed
| | Fetch Using Prefetched List
| | | Prefetch: 150 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 3
| | | Return Data to Application
| | | | #Columns = 3 Return Data Completion
索引スキャンが選択され ているが、実際のデータ ページへのアクセスは パーティション6～10のみ に実行
Regular表とのアクセスプラン比較
1990 OCUSTLIST
表
コンテナー1 コンテナー2 コンテナー3 コンテナー4 SELECT * FROM OCUSTLIST
WHERE SUBSDATE BETWEEN ‘1/1/1996’ AND ’1/5/2000’ and PROVINCE =‘1’
1991 1992
1996 1997 1998
1999 2000 2001 1993
1994 1995
create table ocustlist
(subsdate date, province char(2), accountID int) in ts;
create index idx2 on ocustlist(province);
20万件（各年度2.8万件）を LOAD
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 143 Rows
RETURN ( 1) Cost
I/O
| 18187.5 FETCH ( 2) 927.152 313.288 /----+----¥
23350 280200 RIDSCN DP-TABLE: V9ICHI ( 3) CUSTLIST 559.096
163.18
| 23350 SORT ( 4) 559.094 163.18
| 23350 IXSCAN ( 5) 507.854 163.18
| 280200 INDEX: V9ICHI
IDX2
区分表のケース Total Cost: 927.152
Rows RETURN ( 1) Cost
I/O
| 18187.5 FETCH ( 2) 1342.46 492.634 /---+---¥
56040 280200 IXSCAN TABLE: V9ICHI ( 3) OCUSTLIST 467.52
133.533
| 280200 INDEX: V9ICHI
IDX2