CREATE TABLE TABPLUS2
(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
XY INTEGER,
DES VARCHAR(32),
PRIMARY KEY (X,Y)
) IN USERSPACE1
INSERT INTO TABPLUS2
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;
/*檢示index*/
/*對table做reorgchk*/
DB2 REORGCHK UPDATE STATISTICS ON TABLE ORION.TABPLUS2
/*結果如下圖*/
db2expln -database CDIDB2 -g -statement "select x,sum(xy) from tabplus2 where x=20 and y=40 group by x" -o c:\plus2.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 tabplus2
where x=20 and y=40
group by x
Section Code Page = 1208
Estimated Cost = 28.780834
Estimated Cardinality = 1.000000
Access Table Name = ORION.TABPLUS2 ID = 2,187
| Index Scan: Name = SYSIBM.SQL080624120815130 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: X (Ascending)
| | | 2: Y (Ascending)
| #Columns = 1
| Single Record
| Fully Qualified Unique Key
| #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
| | Row : Next Key Share
Aggregation
| 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
| TABPLUS2
Index:
SYSIBM
SQL080624120815130
