SEO

Hi all,

Db2 v8 FP15 LUW .

create table T (ID varchar (24), ABC timestamp)

There is an index for (ID, ABC), allowing reverse Scans.

My application needs to determine MIN and MAX(ABC) for a given ID. We
are currently using a simple statement:

select MIN(abc), MAX(abc) from T where ID = ? for read only

Table T has 100+ million rows, and several other applications are
reading/deleting data from it. The statement above runs with UR
isolation, however it takes a very long time to complete (5-10
minutes, or more).

I have studied the access plan, and it looks OK:

Access Plan:
-----------

Total Cost: 25.6855
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
25.6855
3.99557
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
12.8262 12.8587
1.99557 2
| |
46.1442 46.1442
IXSCAN IXSCAN
( 4) ( 6)
12.8544 12.8544
2 2
| |
973678 973678
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN


I am looking for some magic SQL or hint that will allow me to improve
this rather 'simple' query.

PS: Yes, table and indexes do have updated statistics.

Thanks in advance,

-Michel

Michel Esber wrote:
> Hi all,
>
> Db2 v8 FP15 LUW .
>
> create table T (ID varchar (24), ABC timestamp)
>
> There is an index for (ID, ABC), allowing reverse Scans.
>
> My application needs to determine MIN and MAX(ABC) for a given ID. We
> are currently using a simple statement:
>
> select MIN(abc), MAX(abc) from T where ID = ? for read only
>
> Table T has 100+ million rows, and several other applications are
> reading/deleting data from it. The statement above runs with UR
> isolation, however it takes a very long time to complete (5-10
> minutes, or more).
>
> I have studied the access plan, and it looks OK:
>
> Access Plan:
> -----------
>
> Total Cost: 25.6855
> Query Degree: 1
>
> Rows
> RETURN
> ( 1)
> Cost
> I/O
> |
> 1
> NLJOIN
> ( 2)
> 25.6855
> 3.99557
> /-----+-----\
> 1 1
> GRPBY GRPBY
> ( 3) ( 5)
> 12.8262 12.8587
> 1.99557 2
> | |
> 46.1442 46.1442
> IXSCAN IXSCAN
> ( 4) ( 6)
> 12.8544 12.8544
> 2 2
> | |
> 973678 973678
> INDEX: RTM INDEX: RTM
> IPSSTAT_WIN IPSSTAT_WIN
>
>
> I am looking for some magic SQL or hint that will allow me to improve
> this rather 'simple' query.
Try this:
SELECT *
FROM (SELECT abc FROM T WHERE ID = ?
ORDER BY abc DESC FETCH FIRST ROW ONLY) AS Z,
(SELECT abc FROM T WHERE ID = ?
ORDER BY abc ASC FETCH FIRST ROW ONLY) AS Y

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

> Try this:
> SELECT *
> * *FROM (SELECT abc FROM T WHERE ID = ?
> * * * * * ORDER BY abc DESC FETCH FIRST ROW ONLY) AS Z,
> * * * * (SELECT abc FROM T WHERE ID = ?
> * * * * * ORDER BY abc ASC FETCH FIRST ROW ONLY) AS Y

Hi Serge, thanks for the quick reply.

I tried your solution, and it has virtually the same cost and plan:


Using MIN/MAX:

Access Plan:
-----------
Total Cost: 25.6855
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
25.6855
3.99557
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
12.8262 12.8587
1.99557 2
| |
46.1442 46.1442
IXSCAN IXSCAN
( 4) ( 6)
12.8544 12.8544
2 2
| |
973678 973678
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN


1) RETURN: (Return Result)
Cumulative Total Cost: 25.6855
Cumulative CPU Cost: 317747
Cumulative I/O Cost: 3.99557
Cumulative Re-Total Cost: 0.037816
Cumulative Re-CPU Cost: 105574
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.6844
Estimated Bufferpool Buffers: 5


Using Fetch First:


Access Plan:
-----------
Total Cost: 25.759
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
25.759
4
/---+---\
1 1
TBSCAN IXSCAN
( 3) ( 6)
12.9045 12.8544
2 2
| |
46.1442 973678
TEMP INDEX: RTM
( 4) IPSSTAT_WIN
12.8585
2
|
46.1442
IXSCAN
( 5)
12.8544
2
|
973678
INDEX: RTM
IPSSTAT_WIN


1) RETURN: (Return Result)
Cumulative Total Cost: 25.759
Cumulative CPU Cost: 443792
Cumulative I/O Cost: 4
Cumulative Re-Total Cost: 0.0678959
Cumulative Re-CPU Cost: 189550
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7305
Estimated Bufferpool Buffers: 3



In fact, using fetch first seems to have more CPU Cost, In terms of
performance (IO/CPU), what benefits should I expect ?

Thanks again, Michel.

Try this then:
SELECT *
FROM (SELECT abc FROM T WHERE ID = ?
ORDER BY id DESC, abc DESC FETCH FIRST ROW ONLY) AS Z,
(SELECT abc FROM T WHERE ID = ?
ORDER BY id ASC, abc ASC FETCH FIRST ROW ONLY) AS Y

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On 25 abr, 12:52, Serge Rielau <srie...@ca.ibm.com> wrote:
> Try this then:
> SELECT *
> * *FROM (SELECT abc FROM T WHERE ID = ?
> * * * * * *ORDER BY id DESC, abc DESC FETCH FIRST ROW ONLY) ASZ,
> * * * * *(SELECT abc FROM T WHERE ID = ?
> * * * * * *ORDER BY id ASC, abc ASC FETCH FIRST ROW ONLY) AS Y


I think it did not help. The plan is a much higher cost:

Access Plan:
-----------
Total Cost: 64906.6
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
64906.6
43706.8
/-----+-----\
1 1
IXSCAN IXSCAN
( 3) ( 4)
32453 32453.6
21853.4 21853.4
| |
1.48278e+06 1.48278e+06
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN




Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statment.


Plan Details:
-------------


1) RETURN: (Return Result)
Cumulative Total Cost: 64906.6
Cumulative CPU Cost: 2.9567e+09
Cumulative I/O Cost: 43706.8
Cumulative Re-Total Cost: 1008.61
Cumulative Re-CPU Cost: 2.66915e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 51.2763
Estimated Bufferpool Buffers: 43707


Thanks for your inputs, Michel.

Hi.

Try this. It will probably generate one indexs can instead of two.

with temp (abc) as
( select abc from T were id = ?)
select max (abc), min(abc) from temp

/dg



"Michel Esber" <michel@us.automatos.com> wrote in message news:d2de1662-4359-4c17-90b0-66daa5f4fc51@b64g2000hsa.googlegroups.com...
On 25 abr, 12:52, Serge Rielau <srie...@ca.ibm.com> wrote:
> Try this then:
> SELECT *
> FROM (SELECT abc FROM T WHERE ID = ?
> ORDER BY id DESC, abc DESC FETCH FIRST ROW ONLY) AS Z,
> (SELECT abc FROM T WHERE ID = ?
> ORDER BY id ASC, abc ASC FETCH FIRST ROW ONLY) AS Y


I think it did not help. The plan is a much higher cost:

Access Plan:
-----------
Total Cost: 64906.6
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
64906.6
43706.8
/-----+-----\
1 1
IXSCAN IXSCAN
( 3) ( 4)
32453 32453.6
21853.4 21853.4
| |
1.48278e+06 1.48278e+06
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN




Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statment.


Plan Details:
-------------


1) RETURN: (Return Result)
Cumulative Total Cost: 64906.6
Cumulative CPU Cost: 2.9567e+09
Cumulative I/O Cost: 43706.8
Cumulative Re-Total Cost: 1008.61
Cumulative Re-CPU Cost: 2.66915e+09
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 51.2763
Estimated Bufferpool Buffers: 43707


Thanks for your inputs, Michel.

> Hi.
>
> Try this. It will probably generate one indexs can instead of two.
>
> with temp (abc) as
> * * ( select abc from T were id *= ?)
> select *max (abc), min(abc) from temp
>
> /dg


DG, DB2 optimized the original statement into two index scans.

Original Statement:
------------------
with temp (COLLECT_TIME) as
(select COLLECT_TIME
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where MACHINE_ID= ? and COLLECT_TIME > ?)
select MIN(COLLECT_TIME), MAX(COLLECT_TIME)
from temp


Optimized Statement:
-------------------
SELECT Q6.$C0, Q3.$C0
FROM
(SELECT MAX(Q2.$C0)
FROM
(SELECT Q1.COLLECT_TIME
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE (:? < Q1.COLLECT_TIME) AND (Q1.MACHINE_ID = :?)) AS Q2) AS
Q3,
(SELECT MIN(Q5.$C0)
FROM
(SELECT Q4.COLLECT_TIME
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q4
WHERE (:? < Q4.COLLECT_TIME) AND (Q4.MACHINE_ID = :?)) AS Q5) AS
Q6

Access Plan:
-----------
Total Cost: 51.2774
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
51.2774
7.9774
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
25.6383 25.6383
3.98869 3.98871
| |
448.726 448.726
IXSCAN IXSCAN
( 4) ( 6)
87.5883 87.5879
13.6267 13.6267
| |
1.48278e+06 1.48278e+06
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN

The overall cost is higher than the original statement

Thanks

Hi!

I tested it on a similar table

Running the inner part ( select abc from T were id = ?) has a cost of 15 timerons
Still db2 arrives at the same type of accesplan as you had with a cost of 30 trs

My table has only 70000 rows. Maybe you get a better plan?
/dg

"Dan van Ginhoven" <danfan46@hotmail.com> wrote in message news:6KnQj.6546$R_4.5328@newsb.telia.net...
> Hi.
>
> Try this. It will probably generate one indexs can instead of two.
>
> with temp (abc) as
> ( select abc from T were id = ?)
> select max (abc), min(abc) from temp
>
> /dg
>
>
>
> "Michel Esber" <michel@us.automatos.com> wrote in message
news:d2de1662-4359-4c17-90b0-66daa5f4fc51@b64g2000hsa.googlegroups.com...
> On 25 abr, 12:52, Serge Rielau <srie...@ca.ibm.com> wrote:
> > Try this then:
> > SELECT *
> > FROM (SELECT abc FROM T WHERE ID = ?
> > ORDER BY id DESC, abc DESC FETCH FIRST ROW ONLY) AS Z,
> > (SELECT abc FROM T WHERE ID = ?
> > ORDER BY id ASC, abc ASC FETCH FIRST ROW ONLY) AS Y
>
>
> I think it did not help. The plan is a much higher cost:
>
> Access Plan:
> -----------
> Total Cost: 64906.6
> Query Degree: 1
>
> Rows
> RETURN
> ( 1)
> Cost
> I/O
> |
> 1
> NLJOIN
> ( 2)
> 64906.6
> 43706.8
> /-----+-----\
> 1 1
> IXSCAN IXSCAN
> ( 3) ( 4)
> 32453 32453.6
> 21853.4 21853.4
> | |
> 1.48278e+06 1.48278e+06
> INDEX: RTM INDEX: RTM
> IPSSTAT_WIN IPSSTAT_WIN
>
>
>
>
> Extended Diagnostic Information:
> --------------------------------
>
> No extended Diagnostic Information for this statment.
>
>
> Plan Details:
> -------------
>
>
> 1) RETURN: (Return Result)
> Cumulative Total Cost: 64906.6
> Cumulative CPU Cost: 2.9567e+09
> Cumulative I/O Cost: 43706.8
> Cumulative Re-Total Cost: 1008.61
> Cumulative Re-CPU Cost: 2.66915e+09
> Cumulative Re-I/O Cost: 0
> Cumulative First Row Cost: 51.2763
> Estimated Bufferpool Buffers: 43707
>
>
> Thanks for your inputs, Michel.
>
>

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 important 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)
Cumulative Total Cost: 15.1599
Cumulative CPU Cost: 121143
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00114287
Cumulative Re-CPU Cost: 3190.64
Cumulative Re-I/O Cost: 0
Cumulative 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)
Cumulative Total Cost: 15.1599
Cumulative CPU Cost: 121143
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00114287
Cumulative Re-CPU Cost: 3190.64
Cumulative Re-I/O Cost: 0
Cumulative 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)
Cumulative Total Cost: 7.5817
Cumulative CPU Cost: 60581.6
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00434154
Cumulative Re-CPU Cost: 12120.6
Cumulative Re-I/O Cost: 0
Cumulative 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)
Cumulative Total Cost: 7.5817
Cumulative CPU Cost: 60589.2
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00434426
Cumulative Re-CPU Cost: 12128.2
Cumulative Re-I/O Cost: 0
Cumulative 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

> This is the optimal plan we want. Can you reproduce it on DB2 V8 with
> this DDL and DML?

Here is a v8 plan using your solution. I will post another message
with the plan for MIN/MAX and group by.

db2 "CREATE TABLE T (ID varchar (24), ABC timestamp)"
db2 "CREATE INDEX I ON T(id, abc) allow reverse scans"
db2 "explain plan for SELECT min, max FROM (SELECT abc as max FROM T
WHERE ID = ? ORDER BY abc DESC FETCH FIRST ROW ONLY) as A , (SELECT
abc as min FROM T WHERE ID = ? ORDER BY abc ASC FETCH FIRST ROW ONLY)
as B"


******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 08.02.8
SOURCE_NAME: SQLC2E07
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2008-04-26-12.07.45.727146
EXPLAIN_REQUESTER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 3.778754e-07
Comm Speed: 0
Buffer Pool size: 165240
Sort Heap size: 1024
Database Heap size: 1024
Lock List size: 100
Maximum Lock List: 10
Average Applications: 1
Locks Available: 1020

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) as A ,
(SELECT abc as min
FROM T
WHERE ID = ?
ORDER BY abc ASC
FETCH FIRST ROW ONLY) as B


Optimized Statement:
-------------------
SELECT Q2.$C0 AS "MIN", Q4.$C0 AS "MAX"
FROM
(SELECT Q1.ABC
FROM DB2INST1.T AS Q1
WHERE (Q1.ID = :?)
ORDER BY Q1.ABC) AS Q2,
(SELECT Q3.ABC
FROM DB2INST1.T AS Q3
WHERE (Q3.ID = :?)
ORDER BY Q3.ABC DESC) AS Q4

Access Plan:
-----------
Total Cost: 12.846
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
12.846
2
/-----+-----\
1 1
IXSCAN IXSCAN
( 3) ( 4)
6.42298 6.42298
1 1
| |
73 73
INDEX: DB2INST1 INDEX: DB2INST1
I I




Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier: 1
Diagnostic Details: EXP0022W Index has no statistics. The index
"DB2INST1"."I" has not had runstats run on it.
This
can lead to poor cardinality and predicate
filtering estimates.

Plan Details:
-------------


1) RETURN: (Return Result)
Cumulative Total Cost: 12.846
Cumulative CPU Cost: 121615
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00933084
Cumulative Re-CPU Cost: 24692.9
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8422
Estimated Bufferpool Buffers: 3

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.2.136 : special_19546
HEAPUSE : (Maximum Statement Heap Usage)
60 Pages
STMTHEAP: (Statement heap size)
4096

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)
Cumulative Total Cost: 12.846
Cumulative CPU Cost: 121615
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00933084
Cumulative Re-CPU Cost: 24692.9
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8422
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)
Cumulative Total Cost: 6.42298
Cumulative CPU Cost: 60803.5
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00466394
Cumulative Re-CPU Cost: 12342.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.42108
Estimated Bufferpool Buffers: 2

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE

Predicates:
----------
2) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q1.ID = :?)

2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q1.ID = :?)


Input Streams:
-------------
1) From Object DB2INST1.I

Estimated number of rows: 73
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)
Cumulative Total Cost: 6.42298
Cumulative CPU Cost: 60811.4
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0046669
Cumulative Re-CPU Cost: 12350.4
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.42108
Estimated Bufferpool Buffers: 2

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
REVERSE
TABLOCK : (Table Lock intent)
INTENT SHARE

Predicates:
----------
3) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q3.ID = :?)

3) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q3.ID = :?)


Input Streams:
-------------
3) From Object DB2INST1.I

Estimated number of rows: 73
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: DB2INST1
Name: T
Type: Table (reference only)

Schema: DB2INST1
Name: I
Type: Index
Time of creation:
2008-04-26-12.06.48.096459
Last statistics update:
Number of columns: 2
Number of rows: 73
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: No
Tablespace name:
IOSTATDATIDX
Tablespace overhead: 6.000000
Tablespace transfer rate: 0.400000
Source for statistics: Single Node
Prefetch page count: 128
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: DB2INST1
Base Table Name: T
Columns in index:
ID
ABC

Base Table For Index Not Already Shown:
---------------------------------------

Schema: DB2INST1
Name: T
Time of creation:
2008-04-26-12.06.12.993169
Last statistics update:
Number of columns: 2
Number of rows: -1
Number of pages: -1
Number of pages with rows: -1
Tablespace name: IOSTATDATIDX
Tablespace overhead: 6.000000
Tablespace transfer rate: 0.400000
Prefetch page count: 128
Container extent page count: 32
Table overflow record count: -1