CREATE TABLE TABPLUS1
(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
XY INTEGER,
DES VARCHAR(32)
) IN USERSPACE1;
/*新增測試資料*/
INSERT INTO TABPLUS1
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;
DB2 REORGCHK UPDATE STATISTICS ON TABLE ORION.TABPLUS1
/*結果如下圖*/
/*SQL Explain Tool*/
db2expln -database CDIDB2 -g -statement "select x,sum(xy) from tabplus1 where x=20 and y=40 group by x" -o c:\plus1.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 tabplus1
where x=20 and y=40
group by x
Section Code Page = 1208
Estimated Cost = 1213.568237
Estimated Cardinality = 1.000000
Access Table Name = ORION.TABPLUS1 ID = 2,191
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 2
| | 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)
|
TBSCAN
( 3)
|
Table:
ORION
TABPLUS1
