Index range scan vs Index fast full scan

This test demonstrates that sql statement should be written in a way that takes advantages of index wisely.

Notice:

where account_name like 'EMP%'; --- good

where substr(account_name, 1,3)='EMP'; --- bad


1. Create a table and index, analyze it.

scott@ORCL> create table transaction as select
2 object_name account_name, last_ddl_time trans_date, object_id amount
3 from all_objects;

Table created.

scott@ORCL> create index transaction_idx on transaction(account_name);

Index created.

scott@ORCL> exec dbms_stats.gather_table_stats(ownname=> 'SCOTT',tabname=> 'TRANSACTION');

PL/SQL procedure successfully completed.


2. Index range scan

scott@ORCL> set autotrace traceonly;
scott@ORCL> select account_name from transaction where account_name like 'EMP%'
 2  /

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3901788871

----------------------------------------------------------------------------
 Id   Operation         Name             Rows   Bytes  Cost (%CPU) Time  
----------------------------------------------------------------------------
   0  SELECT STATEMENT                       2     48      2   (0) 00:00:01
*  1   INDEX RANGE SCAN TRANSACTION_IDX      2     48      2   (0) 00:00:01
----------------------------------------------------------------------------

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

  1 - access("ACCOUNT_NAME" LIKE 'EMP%')
      filter("ACCOUNT_NAME" LIKE 'EMP%')


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       877  bytes sent via SQL*Net to client
       392  bytes received via SQL*Net from client
         3  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        22  rows processed

3. Index fast full scan

scott@ORCL> select account_name from transaction where substr(account_name, 1,3)='EMP';

22 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1729691990

-------------------------------------------------------------------------------
 Id   Operation             Name             Rows   Bytes  Cost (%CPU) Time  
-------------------------------------------------------------------------------
   0  SELECT STATEMENT                         499  11976     58   (6) 00:00:01
*  1   INDEX FAST FULL SCAN TRANSACTION_IDX    499  11976     58   (6) 00:00:01
-------------------------------------------------------------------------------

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

  1 - filter(SUBSTR("ACCOUNT_NAME",1,3)='EMP')


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
       254  consistent gets
         0  physical reads
         0  redo size
       877  bytes sent via SQL*Net to client
       392  bytes received via SQL*Net from client
         3  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        22  rows processed