优化器的问题

diablo2

最初由 askgyliu 发布
[B]/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 >
[/B]

试试 '55.5'

wangzhonnew

关键是
                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)

VS

                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, '..'))

在你的测试里两者的顺序和你调用的顺序相符，但是并不是在所有情况下都一定会相符

wangzhonnew

现在他们使用sargable，所以两种predicate的weight应该相同，所以顺序不作调整，使用你的输入顺序。但是如果一个是start/stop key predicate，另一个是sargable，那么肯定首先apply start/stop key啦

askgyliu

我 只 是 漏 掉 了 一 个 细 节 。 我 创 建 了 一 个 INDEX：

create index idx_t1 on t1(a)



教 训 就 是 ， 千 万 别 利 用 OPTIMIZER的 这 种 predicate sequence来 玩 。

You will never know what will happen, or will there be any change in the optimizer, or a join may totally change the result, which will be very troublesome to debug.

btw, Oracle has the same behavior.

diablo2

最初由 askgyliu 发布
[B]

我 只 是 漏 掉 了 一 个 细 节 。 我 创 建 了 一 个 INDEX：

create index idx_t1 on t1(a)



教 训 就 是 ， 千 万 别 利 用 OPTIMIZER的 这 种 predicate sequence来 玩 。

You will never know what will happen, or will there be any change in the optimizer, or a join may totally change the result, which will be very troublesome to debug.

btw, Oracle has the same behavior. [/B]

你是说在有索引的基础上，不会有我测试的现象？

wangzhonnew

因为有索引会用start/stop key predicate，所以再同时存在start/stop key和SARGable得情况下肯定先做start/stop key

askgyliu

/home/db2inst/Scripts > db2 -v "select * from test0809"
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会从右往左apply the predicates。

/home/db2inst/Scripts > db2 -v "select * from test0809 where a in(2,5) and dec(b,10,5)>0"
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

/home/db2inst/Scripts > db2 -v "select * from test0809 where dec(b,10,5)>0 and a in (2,5)"
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.

但我创建了index on column a之后，DB2 optimizer发现使用index可能会快一些，而刚好在这种情况下after the filtering of column a, all the data in column can be casted to decimal values:

/home/db2inst/Scripts > db2 -v "create index idx_t1 on test0809(a)"
create index idx_t1 on test0809(a)
DB20000I  The SQL command completed successfully.

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

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

  2 record(s) selected.

However, after I did a runstats on the table, DB2 optimizer realize that to fulfill this query for this particular table, accessing by index may be even slower because the tablesize is so small that accessed by index may need two or three physical reads, while accessed by table itself (full table scan) may need only one physical read. And this causes the filtering to be applied from right to the left again, and resulted in error again.

/home/db2inst/Scripts > db2 -v "runstats on table db2.test0809 and indexes all"
runstats on table db2.test0809 and indexes all
DB20000I  The RUNSTATS command completed successfully.

/home/db2inst/Scripts > db2 -v "select * from test0809 where a in(2,5) and dec(b,10,5)>0"
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

所以，我的认识是，如果真的有必要做data type conversion的话，最好确保所有的数据都是真的可以CONVERTED。

diablo2

借兄弟们的精彩解答，给自己授精。哈哈

sunholiday

好难懂哦

RamanBull

受以精华只贴 ~~