反向函数和反向键索引对于前通配符的影响

建立反向键索引
SQL> create index idx_tindex_name on tindex(object_name) reverse;

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LDY',tabname=>'tindex',cascade=>true);

PL/SQL procedure successfully completed.

反向键索引，并不能让前通配符的语句走索引范围扫描，经过测试，是索引快速全扫描，虽然比全表扫描快，但仍然有极大开销

SQL> select object_name from tindex where object_name like '%TINDEX';

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
Plan hash value: 3672022132

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 10158 |   247K|   197   (2)| 00:00:03 |
|*  1 |  INDEX FAST FULL SCAN| IDX_TINDEX_NAME | 10158 |   247K|   197   (2)| 00:00:03 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE '%TINDEX')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1012  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

建立反向函数索引
SQL> create index idx_tindex_name on tindex(reverse(object_name));

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LDY',tabname=>'tindex',cascade=>true);

PL/SQL procedure successfully completed.

使用反向函数，将通配符变为在后面
SQL> select object_name from tindex where reverse(object_name) like 'XEDNIT%';

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3095835948

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     7 |   175 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TINDEX          |     7 |   175 |     8   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TINDEX_NAME |     7 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')
       filter(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed