Oracle Hint "index_combine"对于like的局限性

Oracle Hint "index_combine"对于like的局限性

数据库版本：11.2.0.4.0

今天遇到1条问题SQL，优化遇到一点问题，SQL文本大概如下：

其中，col2和col3存在索引的且选择性都很高，col1的业务特性是只有两个值选择性低没有也不适合建Btree索引。

导致每次执行都是全表扫描，统计发现这条SQL每半小时执行次数大概在1200次左右。

select * from t
where t.col1='1'
and ( instr(t.col2, :1) > 0 or t.col3 = :2 )
order by t.create_time desc;

经过了解绑定变量 :1 从业务看过滤后的结果集并不大。

那么优化思路是将  instr(t.col2, :1) > 0  改为 t.col2 like '%'||:1||'%' ，之后让col2和col3对应索引做位图布尔运算，这样就能够有效避免全表扫描。

但是index_combine无法生效，研究发现index_combine不支持索引字段在谓词条件中使用了like '%%'的形式。。

这里可以创建测试样例表和样例SQL做说明。

create table zkm as select * from dba_objects;
create index idx_do_owner on zkm(owner);      
create index idx_do_object_name on zkm(object_name);

比如下边的第一条SQL(Hint强制index_combine)和执行计划，

以及第二条相同SQL但是Hint不同，单独like '%%'使用索引是没问题的。

select /*+ index_combine(zkm idx_do_owner idx_do_object_name) */ * from zkm                                         select /*+ index(zkm idx_do_object_name) */ * from zkm 
where owner='ZKM' and object_name like '%ZKM%';                                                                     where owner='ZKM' and object_name like '%ZKM%';
                                                                                                                    
Plan hash value: 301967187                                                                                          Plan hash value: 2326426975
                                                                                                                    
---------------------------------------------------------------------------------------------------------------     ---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |     | Id  | Operation                   | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------     ---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |      1 |00:00:00.01 |       6 |      2 |     |   0 | SELECT STATEMENT            |                    |      1 |        |      1 |00:00:00.05 |     242 |    178 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ZKM          |      1 |      1 |      1 |00:00:00.01 |       6 |      2 |     |*  1 |  TABLE ACCESS BY INDEX ROWID| ZKM                |      1 |      1 |      1 |00:00:00.05 |     242 |    178 |
|*  2 |   INDEX RANGE SCAN          | IDX_DO_OWNER |      1 |      9 |      3 |00:00:00.01 |       3 |      2 |     |*  2 |   INDEX FULL SCAN           | IDX_DO_OBJECT_NAME |      1 |   2821 |      3 |00:00:00.05 |     239 |    178 |
---------------------------------------------------------------------------------------------------------------     ---------------------------------------------------------------------------------------------------------------------
                                                                                                                    
Predicate Information (identified by operation id):                                                                 Predicate Information (identified by operation id):
---------------------------------------------------                                                                 ---------------------------------------------------
                                                                                                                    
   1 - filter(("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%ZKM%'))                                              1 - filter("OWNER"='ZKM')
   2 - access("OWNER"='ZKM')                                                                                           2 - filter(("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%ZKM%'))

但是支持like 'x%'的形式是没问题的。

select /*+ index_combine(zkm idx_do_owner idx_do_object_name) */ * from zkm                                                                         select /*+ index_combine(zkm idx_do_owner idx_do_object_name) */ * from zkm
where owner='ZKM' and object_name like 'ZKM%';                                                                                                      where owner='ZKM' or object_name like 'ZKM%';
                                                                                                                                                    
Plan hash value: 98540114                                                                                                                           Plan hash value: 4263379613
                                                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------        -------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |        | Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------        -------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                    |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |        |   0 | SELECT STATEMENT             |                    |      1 |        |      5 |00:00:00.01 |      11 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | ZKM                |      1 |     28 |      1 |00:00:00.01 |       5 |       |       |          |        |   1 |  CONCATENATION               |                    |      1 |        |      5 |00:00:00.01 |      11 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                    |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |        |   2 |   TABLE ACCESS BY INDEX ROWID| ZKM                |      1 |      3 |      3 |00:00:00.01 |       6 |
|   3 |    BITMAP AND                    |                    |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |        |*  3 |    INDEX RANGE SCAN          | IDX_DO_OWNER       |      1 |    274 |      3 |00:00:00.01 |       3 |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                    |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |        |*  4 |   TABLE ACCESS BY INDEX ROWID| ZKM                |      1 |      9 |      2 |00:00:00.01 |       5 |
|*  5 |      INDEX RANGE SCAN            | IDX_DO_OWNER       |      1 |        |      3 |00:00:00.01 |       2 |       |       |          |        |*  5 |    INDEX RANGE SCAN          | IDX_DO_OBJECT_NAME |      1 |    617 |      3 |00:00:00.01 |       2 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                    |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |        -------------------------------------------------------------------------------------------------------------
|   7 |      SORT ORDER BY               |                    |      1 |        |      3 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|        
|*  8 |       INDEX RANGE SCAN           | IDX_DO_OBJECT_NAME |      1 |        |      3 |00:00:00.01 |       2 |       |       |          |        Predicate Information (identified by operation id):
--------------------------------------------------------------------------------------------------------------------------------------------        ---------------------------------------------------
                                                                                                                                                    
Predicate Information (identified by operation id):                                                                                                    3 - access("OWNER"='ZKM')
---------------------------------------------------                                                                                                    4 - filter(LNNVL("OWNER"='ZKM'))
                                                                                                                                                       5 - access("OBJECT_NAME" LIKE 'ZKM%')
   5 - access("OWNER"='ZKM')                                                                                                                               filter("OBJECT_NAME" LIKE 'ZKM%')
   8 - access("OBJECT_NAME" LIKE 'ZKM%')
       filter(("OBJECT_NAME" LIKE 'ZKM%' AND "OBJECT_NAME" LIKE 'ZKM%'))

https://www.cnblogs.com/PiscesCanon/p/18184567

感觉匪夷所思，不应该啊。

也没搜到啥资料。防。

那么对于前边一开始提到的SQL如何优化？

select * from t
where t.col1='1'
and ( instr(t.col2, :1) > 0 or t.col3 = :2 )
order by t.create_time desc;

只能拆开使用union了。

如下(hint看情况，不一定cbo能够自动用上)：

select a* from (
select /*+ index(t1(col2)) */ t1.* from t1
where t1.col1='1'
and t1.col2 like '%'||:1||'%'
union
select /*+ index(t2(col1)) */ t2.* from t2
where t2.col1='1'
and t2.col3 = :2 
) a
order by a.create_time desc;

随便找一组绑定变量代入，执行了下，原SQL跑0.5s左右，

改造成union的形式，跑0.3s左右。

单次的逻辑读消耗也少了4/5多点。

如果表更大的话，就更明显了，毕竟原SQL是全表扫的。