今天有个客户数据库负载比较高，查询发现sql用了 like %ABC% 这种写法，并且应用方面还无法改写成等值条件，下面进行测试。
（客户生产表的这个列唯一值比较高，并且表的列很多有三十多个，如果列比较少或者like %ABC%或取的值比较多的话下面的建议不适用。）

首先创建测试表
create table tt as select * from dba_objects;
SQL> alter system flush buffer_cache;
System altered.
SQL> SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME
FROM TT
WHERE OBJECT_NAME LIKE '%ZC%';


44000 JAVA CLASS
oracle/net/resolver/EZConnectResolver

47203 JAVA CLASS
oracle/jdbc/driver/TSTZCopyingBinder

47205 JAVA CLASS
oracle/jdbc/driver/TSLTZCopyingBinder


Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3664 | 178K| 395 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TT | 3664 | 178K| 395 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
1419 physical reads
0 redo size
915 bytes sent via SQL*Net to client
448 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
未作处理前的consistent gets 大约 1400


进行sql改写，并创建索引，测试性能。

SQL> create index t_name on TT(OBJECT_NAME);
Index created.
SQL> exec dbms_stats.gather_table_stats('ZC','TT',no_invalidate=>false);
PL/SQL procedure successfully completed.



SQL> alter system flush buffer_cache;
System altered.

SQL> SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME
FROM TT
WHERE OBJECT_NAME IN
(SELECT OBJECT_NAME FROM TT WHERE OBJECT_NAME LIKE '%ZC%');

47205 JAVA CLASS
oracle/jdbc/driver/TSLTZCopyingBinder

47203 JAVA CLASS
oracle/jdbc/driver/TSTZCopyingBinder

44000 JAVA CLASS
oracle/net/resolver/EZConnectResolver



Execution Plan
----------------------------------------------------------
Plan hash value: 1780297486
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3664 | 304K| 523 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 3664 | 304K| 523 (1)| 00:00:01 |
|* 2 | INDEX FAST FULL SCAN| T_NAME | 3664 | 125K| 128 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TT | 3664 | 178K| 395 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL)
3 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
487 consistent gets
485 physical reads
0 redo size
915 bytes sent via SQL*Net to client
506 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

sql 改写后 大约490，还是有比较明显的效果的。
或者下面这种写法：

select /*+no_merge(b) use_nl(t b)*/
t.OBJECT_ID, t.OBJECT_TYPE, t.OBJECT_NAME
from TT t, (select rowid from TT a where a.OBJECT_NAME like '%ZC%') b
where t.rowid = b.rowid;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3989423677
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3664 | 264K| 3792 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 3664 | 264K| 3792 (1)| 00:00:01 |
| 2 | VIEW | | 3664 | 43968 | 128 (1)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN | T_NAME | 3664 | 168K| 128 (1)| 00:00:01 |
| 4 | TABLE ACCESS BY USER ROWID| TT | 1 | 62 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
3 - filter("A"."OBJECT_NAME" LIKE '%ZC%' AND "A"."OBJECT_NAME" IS NOT NULL)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / B@SEL$1
U - use_nl(t b)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
479 consistent gets
0 physical reads
0 redo size
915 bytes sent via SQL*Net to client
537 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

consistent gets也比较低。

测试instr函数性能

SQL> alter system flush buffer_cache;
SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE instr(OBJECT_NAME, 'ZC') >0;
System altered.

Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3664 | 178K| 395 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TT | 3664 | 178K| 395 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INSTR("OBJECT_NAME",'ZC')>0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
1419 physical reads
0 redo size
915 bytes sent via SQL*Net to client
447 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

instr和like %ZC% 差距不大，但是如果带入的变量ZC是个定值，我们可以考虑创建基于函数的索引进行优化。

SQL> create index idx_tt2 on tt(instr(OBJECT_NAME,'ZC'));
Index created.
SQL> exec dbms_stats.gather_table_stats('ZC','TT',no_invalidate=>false);
PL/SQL procedure successfully completed.

SQL> set linesize 1000
SQL> SELECT /*+ index (TT IDX_TT2 )*/ OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE INSTR(OBJECT_NAME, 'ZC') >0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1462875822
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54953 | 2844K| 1167 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 54953 | 2844K| 1167 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT2 | 54953 | | 101 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(INSTR("OBJECT_NAME",'ZC')>0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
915 bytes sent via SQL*Net to client
474 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

consistent gets 下降特别明显。

SELECT /*+ index (TT IDX_TT2 )*/ OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE INSTR(OBJECT_NAME, 'ZC') >0，测试的时候发现，这个sql不加hint
不会走索引，怀疑是clustering_factor的原因。

exec dbms_stats.SET_INDEX_STATS('ZC','IDX_TT2', clstfct=>10);

SQL> SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM TT WHERE instr(OBJECT_NAME, 'ZC') >0;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1462875822
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54953 | 2844K| 110 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 54953 | 2844K| 110 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT2 | 54953 | | 101 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access(INSTR("OBJECT_NAME",'ZC')>0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
915 bytes sent via SQL*Net to client
446 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed