建立反向键索引
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