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是全表扫的。
