@dbsnake-用合適的函數索引來避免看似無法避免的全表掃描

Table created.

        ID NAME
---------- -----
         1 abc
         2 bc
         3 c

SQL> select id, name from rev where name like '%bc';
        ID NAME
---------- -----
         1 abc
         2 bc

Execution Plan
----------------------------------------------------------
Plan hash value: 3205185662

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    34 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| REV  |     2 |    34 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME" LIKE '%bc')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        633  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)
          2  rows processed

Index created.

        ID NAME
---------- -----
         2 bc
         1 abc

Execution Plan
----------------------------------------------------------
Plan hash value: 2418054352

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------

   2 - access(REVERSE("NAME") LIKE 'cb%')
       filter(REVERSE("NAME") LIKE 'cb%')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        633  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)
          2  rows processed