CREATE TABLE TABPLUS
(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
XY INTEGER,
DES VARCHAR(32)
) ORGANIZE BY DIMENSIONS (X, Y)
NOT LOGGED INITIALLY IN USERSPACE1;
/*新增測試資料*/
INSERT INTO TABPLUS
WITH N(X) AS (
SELECT 1 X
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.X + 1
FROM N
WHERE N.X + 1 <= 100
)
SELECT N.X , N1.X , N.X * N1.X,
RTRIM(CHAR(N.X)) || ' x ' ||
RTRIM(CHAR(N1.X)) || ' = ' ||
RTRIM(CHAR(N.X * N1.X))
FROM N,N N1;
/*REORGCHK TABPLUS*/
DB2 REORGCHK UPDATE STATISTICS ON TABLE ORION.TABPLUS
/*結果如下圖*/
/*REORGCHK TABPLUS*/
SELECT A.TABNAME,A.CARD * (SUM(B.AVGCOLLEN)+10)
FROM SYSCAT.TABLES A, SYSCAT.COLUMNS B
WHERE A.TABNAME = B.TABNAME
AND B.TABNAME ='TABPLUS'
GROUP BY A.TABNAME,A.CARD
/*執行結果*/
TABPLUS 410000
db2expln -database CDIDB2 -g -statement "select x,sum(xy) from tabplus where x=20 and y=40 group by x" -o c:\plus.log
/*結果如下:*/
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "ORION"
SQL Statement:
select x, sum(xy)
from tabplus
where x=20 and y=40
group by x
Section Code Page = 1208
Estimated Cost = 77.462067
Estimated Cardinality = 1.000000
Access Table Name = ORION.TABPLUS ID = 2,190
| Index Scan: Name = SYSIBM.SQL080624120914310 ID = 1
| | Composite Block Index
| | Index Columns:
| | | 1: X (Ascending)
| | | 2: Y (Ascending)
| #Columns = 1
| Clustered by Dimension for Block Index Access
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: 20
| | | | 2: 40
| | Stop Key: Inclusive Value
| | | | 1: 20
| | | | 2: 40
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Block: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Predicate Aggregation
| | | Group By
| | | Column Function(s)
Aggregation Completion
| Group By
| Column Function(s)
Return Data to Application
| #Columns = 2
End of section
Optimizer Plan:
RETURN
( 1)
|
GRPBY
( 2)
|
FETCH
( 3)
/ \
IXSCAN Table:
( 3) ORION
| TABPLUS
Index:
SYSIBM
SQL080624120914310
※ drop MDC TABLE TABPLUS 前,只剩下12.8G. DROP 後剩下14.0G
結論是我覺得MDC Table佔空間,performance也沒有好到哪裡去. 用MQT是上選.
