Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / DB2 Topics / April 2008

Tip: Looking for answers? Try searching our database.

Question about MIN/MAX optimization

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Michel Esber - 25 Apr 2008 16:05 GMT
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
Reply to this Message
Serge Rielau - 25 Apr 2008 16:15 GMT
> Hi all,
>
[quoted text clipped - 52 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
Michel Esber - 25 Apr 2008 16:41 GMT
> 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.
Reply to this Message
Serge Rielau - 25 Apr 2008 16:52 GMT
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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
Michel Esber - 25 Apr 2008 17:00 GMT
> 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.
Reply to this Message
Dan van Ginhoven - 25 Apr 2008 17:34 GMT
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

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.
Reply to this Message
Michel Esber - 25 Apr 2008 17:58 GMT
> Hi.
>
[quoted text clipped - 5 lines]
>
> /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
Reply to this Message
Serge Rielau - 26 Apr 2008 00:10 GMT
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
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
Michel Esber - 26 Apr 2008 16:18 GMT
> 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
Reply to this Message
Serge Rielau - 26 Apr 2008 16:57 GMT
>> This is the optimal plan we want. Can you reproduce it on DB2 V8 with
>> this DDL and DML?
[quoted text clipped - 25 lines]
>  INDEX: DB2INST1  INDEX: DB2INST1
>         I                I

OK, so now the question is where is the difference between your original
scenario  (not the best plan) and mine (best plan).
I recommend morphing it step by step and see where you loose your way.
Obviosuly the first step is to add data and statistics.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
Michel Esber - 26 Apr 2008 16:20 GMT
> This is the optimal plan we want. Can you reproduce it on DB2 V8 with
> this DDL and DML?

Here is the plan with MIN/MAX:

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(ABC), MAX(ABC)
from T
where ID=?

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

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

             Rows
            RETURN
            (   1)
             Cost
              I/O
              |
               1
            NLJOIN
            (   2)
            12.8453
               2
         /-----+-----\
       1                1
    GRPBY            GRPBY
    (   3)           (   5)
    6.42335          6.42127
       1                1
      |                |
     2.92             2.92
    IXSCAN           IXSCAN
    (   4)           (   6)
    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.8453
               Cumulative CPU Cost:            124849
               Cumulative I/O Cost:            2
               Cumulative Re-Total Cost:       0.0064907
               Cumulative Re-CPU Cost:         17176.8
               Cumulative Re-I/O Cost:         0
               Cumulative First Row Cost:      12.8441
               Estimated Bufferpool Buffers:   3

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

               Input Streams:
               -------------
                       7) From Operator #2

                               Estimated number of rows:       1
                               Number of columns:              2
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q7.$C1+Q7.$C0

       2) NLJOIN: (Nested Loop Join)
               Cumulative Total Cost:          12.8453
               Cumulative CPU Cost:            124849
               Cumulative I/O Cost:            2
               Cumulative Re-Total Cost:       0.0064907
               Cumulative Re-CPU Cost:         17176.8
               Cumulative Re-I/O Cost:         0
               Cumulative First Row Cost:      12.8441
               Estimated Bufferpool Buffers:   3

               Arguments:
               ---------
               EARLYOUT: (Early Out flag)
                       NONE
               FETCHMAX: (Override for FETCH MAXPAGES)
                       IGNORE
               ISCANMAX: (Override for ISCAN MAXPAGES)
                       IGNORE

               Input Streams:
               -------------
                       3) From Operator #3

                               Estimated number of rows:       1
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q3.$C0

                       6) From Operator #5

                               Estimated number of rows:       1
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q6.$C0

               Output Streams:
               --------------
                       7) To Operator #1

                               Estimated number of rows:       1
                               Number of columns:              2
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q7.$C1+Q7.$C0

       3) GRPBY : (Group By)
               Cumulative Total Cost:          6.42335
               Cumulative CPU Cost:            61783.5
               Cumulative I/O Cost:            1
               Cumulative Re-Total Cost:       0.00503426
               Cumulative Re-CPU Cost:         13322.5
               Cumulative Re-I/O Cost:         0
               Cumulative First Row Cost:      6.4226
               Estimated Bufferpool Buffers:   2

               Arguments:
               ---------
               AGGMODE : (Aggregration Mode)
                       COMPLETE
               GROUPBYC: (Group By columns)
                       FALSE
               GROUPBYN: (Number of Group By columns)
                       0
               JN INPUT: (Join input leg)
                       OUTER
               ONEFETCH: (One Fetch flag)
                       FALSE

               Input Streams:
               -------------
                       2) From Operator #4

                               Estimated number of rows:       2.92
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q2.$C0

               Output Streams:
               --------------
                       3) To Operator #2

                               Estimated number of rows:       1
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q3.$C0

       4) 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:
               ---------
               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.$RID$+Q1.ID+Q1.ABC

               Output Streams:
               --------------
                       2) To Operator #3

                               Estimated number of rows:       2.92
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q2.$C0

       5) GRPBY : (Group By)
               Cumulative Total Cost:          6.42127
               Cumulative CPU Cost:            61285.6
               Cumulative I/O Cost:            1
               Cumulative Re-Total Cost:       0.000783828
               Cumulative Re-CPU Cost:         2074.3
               Cumulative Re-I/O Cost:         0
               Cumulative First Row Cost:      6.42117
               Estimated Bufferpool Buffers:   2

               Arguments:
               ---------
               AGGMODE : (Aggregration Mode)
                       COMPLETE
               GROUPBYC: (Group By columns)
                       FALSE
               GROUPBYN: (Number of Group By columns)
                       0
               JN INPUT: (Join input leg)
                       INNER
               ONEFETCH: (One Fetch flag)
                       TRUE

               Input Streams:
               -------------
                       5) From Operator #6

                               Estimated number of rows:       2.92
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q5.$C0(A)

               Output Streams:
               --------------
                       6) To Operator #2

                               Estimated number of rows:       1
                               Number of columns:              1
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
                               ------------
                               +Q6.$C0

       6) 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:
               ---------
               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:
               ----------
               3) Start Key Predicate
                       Relational Operator:            Equal (=)
                       Subquery Input Required:        No
                       Filter Factor:                  0.04

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

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

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

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

                               Estimated number of rows:       73
                               Number of columns:              3
                               Subquery predicate ID:          Not
Applicable

                               Column Names:
            &nb