OK, obviously what you are running is NOT what you are posting as
evidenced by teh changed query.
So let's quit mapping things around and loosing im****tant information in
the process (such as half of the db2exfmt output).
Now, I don't have DB2 V8 handy, so lets level set:
Thsi is my original proposal:
CREATE TABLE T (ID varchar (24), ABC timestamp);
CREATE INDEX I ON T(id, abc);
SELECT min, max FROM (SELECT abc as max FROM T WHERE ID = ? ORDER BY abc
DESC FETCH FIRST ROW ONLY),
(SELECT abc as min FROM T WHERE ID = ? ORDER BY
abc ASC FETCH FIRST ROW ONLY);
This is the explain plan:
Database Context:
----------------
Parallelism: None
CPU Speed: 3.581944e-007
Comm Speed: 100
Buffer Pool size: 1028
Sort Heap size: 55
Database Heap size: 1282
Lock List size: 3916
Maximum Lock List: 98
Average Applications: 1
Locks Available: 122805
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT min, max
FROM
(SELECT abc as max
FROM T
WHERE ID = ?
ORDER BY abc DESC
FETCH FIRST ROW ONLY),
(SELECT abc as min
FROM T
WHERE ID = ?
ORDER BY abc ASC
FETCH FIRST ROW ONLY)
Optimized Statement:
-------------------
SELECT Q2.$C0 AS "MIN", Q4.$C0 AS "MAX"
FROM
(SELECT Q1.ABC
FROM SRIELAU.T AS Q1
WHERE (Q1.ID = :?)
ORDER BY Q1.ABC) AS Q2,
(SELECT Q3.ABC
FROM SRIELAU.T AS Q3
WHERE (Q3.ID = :?)
ORDER BY Q3.ABC DESC) AS Q4
Access Plan:
-----------
Total Cost: 15.1599
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
15.1599
2
/-------+------\
1 1
IXSCAN IXSCAN
( 3) ( 4)
7.5817 7.5817
1 1
| |
70 70
INDEX: SRIELAU INDEX: SRIELAU
I I
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0022W Index has no statistics. The index
"SRIELAU "."I" has not had runstats run on it.
This can lead to poor cardinality and predicate
filtering estimates.
Plan Details:
-------------
1) RETURN: (Return Result)
***ulative Total Cost: 15.1599
***ulative CPU Cost: 121143
***ulative I/O Cost: 2
***ulative Re-Total Cost: 0.00114287
***ulative Re-CPU Cost: 3190.64
***ulative Re-I/O Cost: 0
***ulative First Row Cost: 15.1599
Estimated Bufferpool Buffers: 3
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.965 : s080306
ENVVAR : (Environment Variable)
DB2_SELECTIVITY = ALL
HEAPUSE : (Maximum Statement Heap Usage)
64 Pages
STMTHEAP: (Statement heap size)
2048
Input Streams:
-------------
5) From Operator #2
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.MAX+Q5.MIN
2) NLJOIN: (Nested Loop Join)
***ulative Total Cost: 15.1599
***ulative CPU Cost: 121143
***ulative I/O Cost: 2
***ulative Re-Total Cost: 0.00114287
***ulative Re-CPU Cost: 3190.64
***ulative Re-I/O Cost: 0
***ulative First Row Cost: 15.1599
Estimated Bufferpool Buffers: 3
Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.MIN
4) From Operator #4
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.MAX
Output Streams:
--------------
5) To Operator #1
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.MAX+Q5.MIN
3) IXSCAN: (Index Scan)
***ulative Total Cost: 7.5817
***ulative CPU Cost: 60581.6
***ulative I/O Cost: 1
***ulative Re-Total Cost: 0.00434154
***ulative Re-CPU Cost: 12120.6
***ulative Re-I/O Cost: 0
***ulative First Row Cost: 7.57997
Estimated Bufferpool Buffers: 2
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
Predicates:
----------
2) Start Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
(Q1.ID = :?)
2) Stop Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
(Q1.ID = :?)
Input Streams:
-------------
1) From Object SRIELAU.I
Estimated number of rows: 70
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ABC(A)+Q1.$RID$+Q1.ID
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.MIN
4) IXSCAN: (Index Scan)
***ulative Total Cost: 7.5817
***ulative CPU Cost: 60589.2
***ulative I/O Cost: 1
***ulative Re-Total Cost: 0.00434426
***ulative Re-CPU Cost: 12128.2
***ulative Re-I/O Cost: 0
***ulative First Row Cost: 7.57997
Estimated Bufferpool Buffers: 2
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
REVERSE
SKIP_INS: (Skip Inserted Rows)
TRUE
TABLOCK : (Table Lock intent)
INTENT SHARE
Predicates:
----------
3) Start Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
(Q3.ID = :?)
3) Stop Key Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
(Q3.ID = :?)
Input Streams:
-------------
3) From Object SRIELAU.I
Estimated number of rows: 70
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.ABC(D)+Q3.$RID$+Q3.ID
Output Streams:
--------------
4) To Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.MAX
Objects Used in Access Plan:
---------------------------
Schema: SRIELAU
Name: T
Type: Table (reference only)
Schema: SRIELAU
Name: I
Type: Index
Time of creation: 2008-04-25-19.02.36.718000
Last statistics update:
Number of columns: 2
Number of rows: 70
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Index clustering statistic: 80.000000
Index leaf pages: 2
Index tree levels: 2
Index full key cardinality: 25
Index first key cardinality: 25
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 2
Index page density: 100
Index avg sequential pages: -1
Index avg gap between sequences:-1
Index avg random pages: -1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 0
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: SRIELAU
Base Table Name: T
Columns in index:
ID
ABC
Base Table For Index Not Already Shown:
---------------------------------------
Schema: SRIELAU
Name: T
Time of creation: 2008-04-25-19.02.35.062001
Last statistics update:
Number of data partitions: 1
Number of columns: 2
Number of rows: -1
Number of pages: -1
Number of pages with rows: -1
Table overflow record count: -1
Indexspace name: USERSPACE1
Tablespace name: USERSPACE1
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Prefetch page count: -1
Container extent page count: 32
Long tablespace name: USERSPACE1
---------
This is the optimal plan we want. Can you reproduce it on DB2 V8 with
this DDL and DML?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
|
