Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > IBM DB2 > Re: Question ab...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 3 of 12 Topic 8829 of 9520
Post > Topic >>

Re: Question about MIN/MAX optimization

by Michel Esber <michel@[EMAIL PROTECTED] > Apr 25, 2008 at 08:41 AM

> Try this:
> SELECT *
> =A0 =A0FROM (SELECT abc FROM T WHERE ID =3D ?
> =A0 =A0 =A0 =A0 =A0 ORDER BY abc DESC FETCH FIRST ROW ONLY) AS Z,
> =A0 =A0 =A0 =A0 (SELECT abc FROM T WHERE ID =3D ?
> =A0 =A0 =A0 =A0 =A0 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)
                ***ulative Total Cost:          25.6855
                ***ulative CPU Cost:            317747
                ***ulative I/O Cost:            3.99557
                ***ulative Re-Total Cost:       0.037816
                ***ulative Re-CPU Cost:         105574
                ***ulative Re-I/O Cost:         0
                ***ulative 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)
                ***ulative Total Cost:          25.759
                ***ulative CPU Cost:            443792
                ***ulative I/O Cost:            4
                ***ulative Re-Total Cost:       0.0678959
                ***ulative Re-CPU Cost:         189550
                ***ulative Re-I/O Cost:         0
                ***ulative 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.
 




 12 Posts in Topic:
Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-25 08:05:00 
Re: Question about MIN/MAX optimization
Serge Rielau <srielau@  2008-04-25 11:15:27 
Re: Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-25 08:41:47 
Re: Question about MIN/MAX optimization
Serge Rielau <srielau@  2008-04-25 11:52:07 
Re: Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-25 09:00:01 
Re: Question about MIN/MAX optimization
"Dan van Ginhoven&qu  2008-04-25 16:34:10 
Re: Question about MIN/MAX optimization
"Dan van Ginhoven&qu  2008-04-25 17:03:10 
Re: Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-25 09:58:22 
Re: Question about MIN/MAX optimization
Serge Rielau <srielau@  2008-04-25 19:10:25 
Re: Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-26 08:18:19 
Re: Question about MIN/MAX optimization
Serge Rielau <srielau@  2008-04-26 11:57:00 
Re: Question about MIN/MAX optimization
Michel Esber <michel@[  2008-04-26 08:20:15 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Tue Dec 2 20:53:37 CST 2008.