我们先创建测试表 T。

    1. create table t as select * from dba_objects;
    1. Table created.

    现在有如下语句。

    1. select * from t where object_name like '%SEQ%';

    因为需要对字符串两边进行模糊匹配，而索引根块和分支块存储的是前缀数据（也就是说object like 'SEQ%'才能走索引），所以上面 SQL 查询无法走索引。

    如果强制走索引，会走 INDEX FULL SCAN。

    1. create index idx_ojbname on t(object_name);
    1. Index created.

    查看强制走索引的执行计划。

    1. select /*+ index(t) */ * from t where object_name like '%SEQ%';
    1. 208 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 3894507753
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                   |Name       | Rows  | Bytes | Cost(%CPU)|Time    |
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT            |           |   219 | 45333 |  2214  (1)|00:00:27|
    11. |  1 |  TABLE ACCESS BY INDEX ROWID|T          |   219 | 45333 |  2214  (1)|00:00:27|
    12. |* 2 |   INDEX FULL SCAN           |IDX_OJBNAME|  3395 |       |   362  (1)|00:00:05|
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - filter("OBJECT_NAME" LIKE '%SEQ%')

    INDEX FULL SCAN 是单块读，性能不如全表扫描。大家可能会有疑问，可不可以走 INDEX FAST FULL SCAN 呢？答案是不可以，因为 INDEX FAST FULL SCAN 不能回表，而上面 SQL 查询需要回表（select *）。

    我们可以创建一个表当索引用，用来代替 INDEX FAST FULL SCAN 不能回表的情况。

    1. create table index_t as select object_name,rowid rid from t;
    1. Table created.

    现在将 SQL 查询改写为如下 SQL。

    1. select *
    2.   from t
    3.  where rowid in (select rid from index_t where object_name like '%SEQ%');

    改写完 SQL 之后，需要让 index_t 与 t 走嵌套循环，同时让 index_t 作为嵌套循环驱动表，这样就达到了让 index_t 充当索引的目的。

    现在我们来对比两个 SQL 的 autotrace 执行计划。

    1. select * from t where object_name like '%SEQ%';
    1. 208 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1601196873
    7. --------------------------------------------------------------------------
    8. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    9. --------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT  |      |   135 | 27945 |   235   (1)| 00:00:03 |
    11. |*  1 |  TABLE ACCESS FULL| T    |   135 | 27945 |   235   (1)| 00:00:03 |
    12. --------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%SEQ%')
    19. Note
    20. -----
    21.    - dynamic sampling used for this statement (level=2)
    23. Statistics
    24. ----------------------------------------------------------
    25.           5  recursive calls
    26.           0  db block gets
    27.        1117  consistent gets
    28.           0  physical reads
    29.           0  redo size
    30.       12820  bytes sent via SQL*Net to client
    31.         563  bytes received via SQL*Net from client
    32.          15  SQL*Net roundtrips to/from client
    33.           0  sorts (memory)
    34.           0  sorts (disk)
    35.         208  rows processed
    1.  select /*+ leading(index_t@a) use_nl(index_t@a,t) */
    2.    *
    3.     from t
    4.    where rowid in (select /*+ qb_name(a) */
    5.                      rid
    6.                       from index_t
    7.                      where object_name like '%SEQ%');
    1. 208 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 2608052908
    6. -------------------------------------------------------------------------------------
    7. | Id | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    8. -------------------------------------------------------------------------------------
    9. |  0 | SELECT STATEMENT            |         |    87 | 25839 |   140   (2)| 00:00:02 |
    10. |  1 |  NESTED LOOPS               |         |    87 | 25839 |   140   (2)| 00:00:02 |
    11. |  2 |   SORT UNIQUE               |         |    87 |  6786 |    95   (2)| 00:00:02 |
    12. |* 3 |    TABLE ACCESS FULL        | INDEX_T |    87 |  6786 |    95   (2)| 00:00:02 |
    13. |  4 |   TABLE ACCESS BY USER ROWID| T       |     1 |   219 |     1   (0)| 00:00:01 |
    14. -------------------------------------------------------------------------------------
    16. Predicate Information (identified by operation id):
    17. ---------------------------------------------------
    19.    3 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%SEQ%')
    21. Note
    22. -----
    23.    - dynamic sampling used for this statement (level=2)
    25. Statistics
    26. ----------------------------------------------------------
    27.           0  recursive calls
    28.           0  db block gets
    29.         499  consistent gets
    30.           0  physical reads
    31.           0  redo size
    32.       12820  bytes sent via SQL*Net to client
    33.         563  bytes received via SQL*Net from client
    34.          15  SQL*Net roundtrips to/from client
    35.           1  sorts (memory)
    36.           0  sorts (disk)
    37.         208  rows processed

    因为 t 表很小，表字段也不多，所以大家可能感觉性能提升不是特别大。当 t 表越大，性能提升就越明显。采用这个方法还需要对 index_t 进行数据同步，我们可以将 index_t 创建为物化视图，刷新方式采用 on commit 刷新。