优化器的问题

diablo2

$ db2 "create table test0809 (a int, b char(10), c int)"
DB20000I  The SQL command completed successfully.
<insert 步骤省略了>
$  db2 "select * from test0809 "

A           B          C
----------- ---------- -----------
          1 121@1                1
          2 22                   0
          1 11                   0
          3 3*3                  1
          3 33                   0
          4 3*3                  0
          5 55.5                 0

  7 record(s) selected.

$ db2 "select * from test0809 where a in(2,5) and dec(b,10,5)>0"

A           B          C
----------- ---------- -----------
SQL0420N  Invalid character found in a character string argument of the
function "DECIMAL".  SQLSTATE=22018
$  db2 "select * from test0809 where dec(b,10,5)>0 and a in(2,5)"

A           B          C
----------- ---------- -----------
          2 22                   0
          5 55.5                 0

  2 record(s) selected.


$

diablo2

我的认识是： 优化器 从右往左 解析，所以造成了上述的错误。  大家接着讨论

myfriend2010

怎么解释？

zzrisme

我加了个条件：
C:\Documents and Settings\administrator>db2 "select * from test0809 where a in (2,5) and dec(b,10,5)>0 and c=0"

A           B          C
----------- ---------- -----------
          2 22                   0
SQL0420N  在函数 "DECIMAL" 的字符串自变量中发现无效字符。  SQLSTATE=22018

应该是说明从右往左的。

wangzhonnew

看access plan先，这个和parse的顺序无关，和执行时apply predicate的顺序有关

diablo2

最初由 wangzhonnew 发布
[B]看access plan先，这个和parse的顺序无关，和执行时apply predicate的顺序有关 [/B]

两个是一样的 AP

[PHP]$ db2expln -d sample -t -g -q "select * from test0809 where a in(2,5) and dec(b,10,5)>0"

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "DB2INST1"


SQL Statement:

  select *
  from test0809
  where a in(2, 5)and dec(b, 10, 5)>0


Section Code Page = 819

Estimated Cost = 12.872317
Estimated Cardinality = 0.666667

Access Table Name = DB2INST1.TEST0809  ID = 4,67
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 2
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion

End of section


Optimizer Plan:

  RETURN
  (   1)
    |
  TBSCAN
  (   2)
    |
Table:
DB2INST1
TEST0809


[/PHP]

[PHP]db2expln -d sample -t -g -q "select * from test0809 where dec(b,10,5)>0 and a in(2,5)"

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "DB2INST1"


SQL Statement:

  select *
  from test0809
  where dec(b, 10, 5)>0 and a in(2, 5)


Section Code Page = 819

Estimated Cost = 12.871552
Estimated Cardinality = 0.666667

Access Table Name = DB2INST1.TEST0809  ID = 4,67
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 2
|  |  Return Data to Application
|  |  |  #Columns = 3
Return Data Completion

End of section


Optimizer Plan:

  RETURN
  (   1)
    |
  TBSCAN
  (   2)
    |
Table:
DB2INST1
TEST0809


[/PHP]

wangzhonnew

apply predicate 的顺序呢？
也就是db2exfmt里面
  TBSCAN
  (   2)

的具体apply predicate的那一段

diablo2

[PHP]
$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
$ db2 "select * from test0809 where a in(2,5) and dec(b,10,5)>0"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604
$ db2 set current explain mode no
DB20000I  The SQL command completed successfully.
$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -t
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



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

DB2_VERSION:            08.02.4
SOURCE_NAME:            SQLC2E07
SOURCE_SCHEMA:          NULLID
SOURCE_VERSION:
EXPLAIN_TIME:           2007-08-10-13.15.52.893295
EXPLAIN_REQUESTER:      DB2INST1

Database Context:
----------------
        Parallelism:            None
        CPU Speed:              3.070237e-07
        Comm Speed:             100
        Buffer Pool size:       40800
        Sort Heap size:         2056
        Database Heap size:     1200
        Lock List size:         2500
        Maximum Lock List:      75
        Average Applications:   1
        Locks Available:        191250

Package Context:
---------------
        SQL Type:               Dynamic
        Optimization Level:     5
        Blocking:               Block All Cursors
        Isolation Level:        Cursor Stability



---------------- STATEMENT 1  SECTION 201 ----------------
        QUERYNO:                3
        QUERYTAG:               CLP
        Statement Type:         Select
        Updatable:              No
        Deletable:              No
        Query Degree:           1

Original Statement:
------------------
select *
from test0809
where a in(2,5) and dec(b,10,5)>0


Optimized Statement:
-------------------
SELECT Q1.A AS "A", Q1.B AS "B", Q1.C AS "C"
FROM DB2INST1.TEST0809 AS Q1
WHERE (0 < DEC(Q1.B, '..')) AND Q1.A IN (2, 5)

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

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
    0.666667
     TBSCAN
     (   2)
     12.8723
        1
       |
        7
TABLE: DB2INST1
    TEST0809




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

No extended Diagnostic Information for this statment.


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


        1) RETURN: (Return Result)
                Cumulative Total Cost:          12.8723
                Cumulative CPU Cost:            72688.3
                Cumulative I/O Cost:            1
                Cumulative Re-Total Cost:       0.00770485
                Cumulative Re-CPU Cost:         25095.3
                Cumulative Re-I/O Cost:         0
                Cumulative First Row Cost:      12.8714
                Estimated Bufferpool Buffers:   1

                Arguments:
                ---------
                BLDLEVEL: (Build level)
                        DB2 v8.1.1.104 : s060120
                STMTHEAP: (Statement heap size)
                        2048

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

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

                                Column Names:
                                ------------
                                +Q2.C+Q2.B+Q2.A


        2) TBSCAN: (Table Scan)
                Cumulative Total Cost:          12.8723
                Cumulative CPU Cost:            72688.3
                Cumulative I/O Cost:            1
                Cumulative Re-Total Cost:       0.00770485
                Cumulative Re-CPU Cost:         25095.3
                Cumulative Re-I/O Cost:         0
                Cumulative First Row Cost:      12.8714
                Estimated Bufferpool Buffers:   1

                Arguments:
                ---------
                MAXPAGES: (Maximum pages for prefetch)
                        ALL
                PREFETCH: (Type of Prefetch)
                        NONE
                ROWLOCK : (Row Lock intent)
                        NEXT KEY SHARE
                SCANDIR : (Scan Direction)
                        FORWARD
                TABLOCK : (Table Lock intent)
                        INTENT SHARE
                TBISOLVL: (Table access Isolation Level)
                        CURSOR STABILITY

                Predicates:
                ----------
                2) Sargable Predicate
                        Relational Operator:            Less Than (<
                        Subquery Input Required:        No
                        Filter Factor:                  0.333333

                        Predicate Text:
                        --------------
                        (0 < DEC(Q1.B, '..'))

                3) Sargable Predicate
                        Relational Operator:            In List (IN)
                        Subquery Input Required:        No
                        Filter Factor:                  0.285714

                        Predicate Text:
                        --------------
                        Q1.A IN (2, 5)


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

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

                                Column Names:
                                ------------
                                +Q1.$RID$+Q1.C+Q1.B+Q1.A


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

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

                                Column Names:
                                ------------
                                +Q2.C+Q2.B+Q2.A


Objects Used in Access Plan:
---------------------------

        Schema: DB2INST1
        Name:   TEST0809
        Type:   Table
                        Time of creation:               2007-08-09-10.40.40.396120
                        Last statistics update:         2007-08-09-11.06.27.422265
                        Number of columns:              3
                        Number of rows:                 7
                        Width of rows:                  27
                        Number of buffer pool pages:    1
                        Distinct row values:            No
                        Tablespace name:                QCASN_DW_TC_M6101
                        Tablespace overhead:            12.670000
                        Tablespace transfer rate:       0.180000
                        Source for statistics:          Single Node
                        Prefetch page count:            32
                        Container extent page count:    32
                        Table overflow record count:    0
                        Table Active Blocks:            -1
Executing Connect Reset -- Connect Reset was Successful.



[/PHP]

[PHP]
$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
$ db2 "select * from test0809 where dec(b,10,5)>0 and a in(2,5)"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604
$ db2 set current explain mode no
DB20000I  The SQL command completed successfully.
$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -t
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



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

DB2_VERSION:            08.02.4
SOURCE_NAME:            SQLC2E07
SOURCE_SCHEMA:          NULLID
SOURCE_VERSION:
EXPLAIN_TIME:           2007-08-10-13.18.03.125024
EXPLAIN_REQUESTER:      DB2INST1

Database Context:
----------------
        Parallelism:            None
        CPU Speed:              3.070237e-07
        Comm Speed:             100
        Buffer Pool size:       40800
        Sort Heap size:         2056
        Database Heap size:     1200
        Lock List size:         2500
        Maximum Lock List:      75
        Average Applications:   1
        Locks Available:        191250

Package Context:
---------------
        SQL Type:               Dynamic
        Optimization Level:     5
        Blocking:               Block All Cursors
        Isolation Level:        Cursor Stability



---------------- STATEMENT 1  SECTION 201 ----------------
        QUERYNO:                5
        QUERYTAG:               CLP
        Statement Type:         Select
        Updatable:              No
        Deletable:              No
        Query Degree:           1

Original Statement:
------------------
select *
from test0809
where dec(b,10,5)>0 and a in(2,5)


Optimized Statement:
-------------------
SELECT Q1.A AS "A", Q1.B AS "B", Q1.C AS "C"
FROM DB2INST1.TEST0809 AS Q1
WHERE Q1.A IN (2, 5) AND (0 < DEC(Q1.B, '..'))

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

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
    0.666667
     TBSCAN
     (   2)
     12.8716
        1
       |
        7
TABLE: DB2INST1
    TEST0809




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

No extended Diagnostic Information for this statment.


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


        1) RETURN: (Return Result)
                Cumulative Total Cost:          12.8716
                Cumulative CPU Cost:            70198.3
                Cumulative I/O Cost:            1
                Cumulative Re-Total Cost:       0.00694036
                Cumulative Re-CPU Cost:         22605.3
                Cumulative Re-I/O Cost:         0
                Cumulative First Row Cost:      12.8707
                Estimated Bufferpool Buffers:   1

                Arguments:
                ---------
                BLDLEVEL: (Build level)
                        DB2 v8.1.1.104 : s060120
                STMTHEAP: (Statement heap size)
                        2048

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

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

                                Column Names:
                                ------------
                                +Q2.C+Q2.B+Q2.A


        2) TBSCAN: (Table Scan)
                Cumulative Total Cost:          12.8716
                Cumulative CPU Cost:            70198.3
                Cumulative I/O Cost:            1
                Cumulative Re-Total Cost:       0.00694036
                Cumulative Re-CPU Cost:         22605.3
                Cumulative Re-I/O Cost:         0
                Cumulative First Row Cost:      12.8707
                Estimated Bufferpool Buffers:   1

                Arguments:
                ---------
                MAXPAGES: (Maximum pages for prefetch)
                        ALL
                PREFETCH: (Type of Prefetch)
                        NONE
                ROWLOCK : (Row Lock intent)
                        NEXT KEY SHARE
                SCANDIR : (Scan Direction)
                        FORWARD
                TABLOCK : (Table Lock intent)
                        INTENT SHARE
                TBISOLVL: (Table access Isolation Level)
                        CURSOR STABILITY

                Predicates:
                ----------
                2) Sargable Predicate
                        Relational Operator:            In List (IN)
                        Subquery Input Required:        No
                        Filter Factor:                  0.285714

                        Predicate Text:
                        --------------
                        Q1.A IN (2, 5)

                3) Sargable Predicate
                        Relational Operator:            Less Than (<
                        Subquery Input Required:        No
                        Filter Factor:                  0.333333

                        Predicate Text:
                        --------------
                        (0 < DEC(Q1.B, '..'))


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

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

                                Column Names:
                                ------------
                                +Q1.$RID$+Q1.C+Q1.A+Q1.B


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

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

                                Column Names:
                                ------------
                                +Q2.C+Q2.B+Q2.A


Objects Used in Access Plan:
---------------------------

        Schema: DB2INST1
        Name:   TEST0809
        Type:   Table
                        Time of creation:               2007-08-09-10.40.40.396120
                        Last statistics update:         2007-08-09-11.06.27.422265
                        Number of columns:              3
                        Number of rows:                 7
                        Width of rows:                  27
                        Number of buffer pool pages:    1
                        Distinct row values:            No
                        Tablespace name:                QCASN_DW_TC_M6101
                        Tablespace overhead:            12.670000
                        Tablespace transfer rate:       0.180000
                        Source for statistics:          Single Node
                        Prefetch page count:            32
                        Container extent page count:    32
                        Table overflow record count:    0
                        Table Active Blocks:            -1
Executing Connect Reset -- Connect Reset was Successful.







[/PHP]

diablo2

从上面的输出看，应该是从右到左吧

askgyliu

/home/db2inst/Scripts > db2 -v "create table t1 (a int, b char(10), c int)"
create table t1 (a int, b char(10), c int)
DB20000I  The SQL command completed successfully.

/home/db2inst/Scripts > db2 -v "insert into t1 values(1,'1@', 1)"
insert into t1 values(1,'1@', 1)
DB20000I  The SQL command completed successfully.

/home/db2inst/Scripts > db2 -v "insert into t1 values(2,'12', 0)"
insert into t1 values(2,'12', 0)
DB20000I  The SQL command completed successfully.

/home/db2inst/Scripts > db2 -v "insert into t1 values(5,'55', 2)"
insert into t1 values(5,'55', 2)
DB20000I  The SQL command completed successfully.

/home/db2inst/Scripts > db2 -v "select * from t1 where a in(2,5) and dec(b,10,5)>0"
select * from t1 where a in(2,5) and dec(b,10,5)>0

A           B          C
----------- ---------- -----------
          2 12                   0
          5 55                   2

  2 record(s) selected.


/home/db2inst/Scripts >