Re: Question about MIN/MAX optimization

> 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)
                ***ulative Total Cost:          12.846
                ***ulative CPU Cost:            121615
                ***ulative I/O Cost:            2
                ***ulative Re-Total Cost:       0.00933084
                ***ulative Re-CPU Cost:         24692.9
                ***ulative Re-I/O Cost:         0
                ***ulative 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)
                ***ulative Total Cost:          12.846
                ***ulative CPU Cost:            121615
                ***ulative I/O Cost:            2
                ***ulative Re-Total Cost:       0.00933084
                ***ulative Re-CPU Cost:         24692.9
                ***ulative Re-I/O Cost:         0
                ***ulative 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)
                ***ulative Total Cost:          6.42298
                ***ulative CPU Cost:            60803.5
                ***ulative I/O Cost:            1
                ***ulative Re-Total Cost:       0.00466394
                ***ulative Re-CPU Cost:         12342.5
                ***ulative Re-I/O Cost:         0
                ***ulative 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)
                ***ulative Total Cost:          6.42298
                ***ulative CPU Cost:            60811.4
                ***ulative I/O Cost:            1
                ***ulative Re-Total Cost:       0.0046669
                ***ulative Re-CPU Cost:         12350.4
                ***ulative Re-I/O Cost:         0
                ***ulative 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