如何优化like '%db2china%'这种谓词的SQL查询

回复 27# ljbupc
在db世界， 这基本是标准化的SQL tuning practice for many many years。
就是说，every developer should know to use index:
避免写
stringfunction(d.name)='chao'
应该写
d.name like '%chao%'收起

回复 24# drdb2

我觉得可以理解为不是一般认为的按照索引顺序去扫描，而是全索引扫描。收起

回复 24# drdb2
嗯，DB2优化器足够的智能收起

回复 5# wangzk0206


    不等价收起

原问题说：
“众所周知,这种语句走不了索引,。。。”
这肯定是不对的。
optimizer 会”smart enough“ 选择 index scan whenever appropriate.收起

回复 22# drdb2
我试了一下楼上的方法，我的t表总行数为 518。将WHERE中使用函数，一般情况下在索引字段上使用函数是不走索引的，但他在这里一样还是走索引了。我的DB2版本是：
DB21085I  实例 "DB2_01" 使用 "32" 位和 DB2 代码发行版 "SQL09074"，级别标识为
"08050107"。
参考标记为 "DB2 v9.7.400.501"、"s110330" 和 "IP23237"，修订包为 "4"。
产品使用 DB2 副本名 "DB2COPY1" 安装在 "C:\PROGRA~1\IBM\SQLLIB~1" 中。


db2expln -d sample -q "select owner,status,card from t where instr(tabname,'EMPLO')>0 " -g -t -i
执行计划如下：
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY 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", "SYSIBMADM",
                                   "ADMINISTRATOR"


Statement:

  select owner, status, card
  from t
  where instr(tabname, 'EMPLO' )>0


Section Code Page = 1208

Estimated Cost = 47.892094
Estimated Cardinality = 172.666656

(    5) Access Table Name = ADMINISTRATOR.T  ID = 7,42
        |  Index Scan:  Name = ADMINISTRATOR.I2  ID = 1
        |  |  Regular Index (Not Clustered)
        |  |  Index Columns:
        |  |  |  1: TABNAME (Ascending)
        |  #Columns = 0
        |  Skip Inserted Rows
        |  Avoid Locking Committed Data
        |  Currently Committed for Cursor Stability
        |  #Key Columns = 0
        |  |  Start Key: Beginning of Index
        |  |  Stop Key: End of Index
        |  Index-Only Access
        |  Index Prefetch: None
        |  Isolation Level: Uncommitted Read
        |  Lock Intents
        |  |  Table: Intent None
        |  |  Row  : None
        |  Sargable Index Predicate(s)
        |  |  #Predicates = 1
(    4) |  |  Insert Into Sorted Temp Table  ID = t1
        |  |  |  #Columns = 1
        |  |  |  #Sort Key Columns = 1
        |  |  |  |  Key 1: (Ascending)
        |  |  |  Sortheap Allocation Parameters:
        |  |  |  |  #Rows     = 173.000000
        |  |  |  |  Row Width = 20
        |  |  |  Piped
        |  |  |  Duplicate Elimination
(    4) Sorted Temp Table Completion  ID = t1
(    3) List Prefetch Preparation
(    3) |  Access Table Name = ADMINISTRATOR.T  ID = 7,42
        |  |  #Columns = 3
        |  |  Skip Inserted Rows
        |  |  Avoid Locking Committed Data
        |  |  Currently Committed for Cursor Stability
        |  |  RID List Fetch Scan
        |  |  Fetch Using Prefetched List
        |  |  |  Prefetch: 28 Pages
        |  |  Lock Intents
        |  |  |  Table: Intent Share
        |  |  |  Row  : Next Key Share
        |  |  Sargable Predicate(s)
        |  |  |  #Predicates = 1
(    1) |  |  |  Return Data to Application
        |  |  |  |  #Columns = 3
(    1) Return Data Completion

End of section


Optimizer Plan:

              Rows
            Operator
              (ID)
              Cost

            172.667
              n/a
            RETURN
             ( 1)
            47.8921
              |
            172.667
              n/a
             FETCH
             (--)
            47.8921
           /       \
    172.667       518
      n/a         n/a
    RIDSCN   Table:
     ( 3)    ADMINISTRATOR
     15.76   T
      |
    172.667
      n/a
     SORT
     ( 4)
    15.7595
      |
    172.667
      n/a
    IXSCAN
     ( 5)
    15.7063
      |
      502
Index:
ADMINISTRATOR
I2


其实从like 或 instr来看（也可以推广到其他的使用函数对索引字段的操作的情况），DB2优化还是鼓励走索引的，请细看其中的第（5）步：
(    5) Access Table Name = ADMINISTRATOR.T  ID = 7,42
        |  Index Scan:  Name = ADMINISTRATOR.I2  ID = 1
        |  |  Regular Index (Not Clustered)
        |  |  Index Columns:
        |  |  |  1: TABNAME (Ascending)
        |  #Columns = 0
        |  Skip Inserted Rows
        |  Avoid Locking Committed Data
        |  Currently Committed for Cursor Stability
        |  #Key Columns = 0
        |  |  Start Key: Beginning of Index
        |  |  Stop Key: End of Index
        |  Index-Only Access
        |  Index Prefetch: None
        |  Isolation Level: Uncommitted Read
        |  Lock Intents
        |  |  Table: Intent None
        |  |  Row  : None
        |  Sargable Index Predicate(s)
        |  |  #Predicates = 1

对此谓词执行的是"Sargable Index Predicate(s)"（此类谓词在性能上比“定界谓词”性能还是差了很多），使用start key/stop key，但他是扫描所有索引叶子节点（索引全扫描），即非匹配扫描。收起

As I mentioned previously, DB2 can use index in some cases.
Below is a simulation I performed and you can experiment as well:

[db2inst1@q1 ~]$ db2 "create table t like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@q1 ~]$ db2 "insert into t select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@q1 ~]$
[db2inst1@q1 ~]$ db2 "create index i2 on t (tabname)"
DB20000I  The SQL command completed successfully.
[db2inst1@q1 ~]$ db2 RUNSTATS ON TABLE "DB2INST1".T FOR SAMPLED DETAILED INDEX "DB2INST1"."I2" ;
DB20000I  The RUNSTATS command completed successfully.

[db2inst1@q1 ~]$ db2expln -d sample -q "select owner,status,card from t where tabname like '%EMPLO%'" -g -t
[......]
==================== 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", "SYSIBMADM",
                                   "DB2INST1"


Statement:
  
  select owner, status, card
  from t
  where tabname like '%EMPLO%'


Section Code Page = 1208

Estimated Cost = 97.380775
Estimated Cardinality = 14.577477

Access Table Name = DB2INST1.T  ID = 3,23
|  Index Scan:  Name = DB2INST1.I2  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: TABNAME (Ascending)
|  #Columns = 0
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  #Key Columns = 0
|  |  Start Key: Beginning of Index
|  |  Stop Key: End of Index
|  Index-Only Access
|  Index Prefetch: Sequential(2), Readahead
|  Isolation Level: Uncommitted Read
|  Lock Intents
|  |  Table: Intent None
|  |  Row  : None
|  Sargable Index Predicate(s)
|  |  #Predicates = 1
|  |  Insert Into Integer Sorted Temp Table  ID = t1
|  |  |  #Columns = 1
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 15.000000
|  |  |  |  Row Width = 20
|  |  |  Duplicate Elimination
Integer Sorted Temp Table Completion  ID = t1
List Prefetch Preparation
|  Access Table Name = DB2INST1.T  ID = 3,23
|  |  #Columns = 3
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  RID List Fetch Scan
|  |  Fetch Using Prefetched List
|  |  |  Prefetch: 11 Pages
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
|  |  |  Return Data to Application
|  |  |  |  #Columns = 3
Return Data Completion

End of section


Optimizer Plan:

       Operator
         (ID)   
               
        RETURN
         ( 1)  
         |   
        FETCH
        (--)  
       /     \
  RIDSCN  Table:   
   ( 3)   DB2INST1
    |     T        
   SORT
   ( 4)
    |   
  IXSCAN
   ( 5)  
    |     
Index:   
DB2INST1
I2收起

For such simple query, Oracle surely will use index to scan.
Do not understand why DB2 wouldn't do so, which definitely casts doubt about the
DB2 optimizer: is it really better or superior than Oracle's?收起

4 楼是正解吧，如果这种查询用的很多的话。

DB2 Text Search 做的挺好的，咱有一整年做这个的开发，分区表的支持是咱开发的。收起

回复 7# 欧阳峰


    这样是匹配几次啊，不也是全表的查询吗收起