About function based index

SQL> create table stu (f1 number, f2 varchar2(10));

Table created.

SQL> insert into stu values(100,'want');

1 row created.

SQL> insert into stu values(101,'ye');

1 row created.

SQL> insert into stu values(103,'li');

1 row created.

SQL> set autotrace on
SQL> delete from stu where f2='ye';

1 row deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1645979371

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | DELETE | STU | | | | |
|* 2 | TABLE ACCESS FULL| STU | 1 | 7 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("F2"='ye')

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


Statistics
----------------------------------------------------------
28 recursive calls
1 db block gets
18 consistent gets
0 physical reads
320 redo size
830 bytes sent via SQL*Net to client
725 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> insert into stu values(102,'ye');

1 row created.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------


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

SQL> set autotrace off
SQL> select * from stu;

F1 F2
---------- ----------
100 want
103 li
102 ye

SQL> create index f2_idx on stu(upper(f2));

Index created.

SQL> set autotrace on
SQL> select * from stu where f2='ye';

F1 F2
---------- ----------
102 ye


Execution Plan
----------------------------------------------------------
Plan hash value: 2614136206

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| STU | 1 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("F2"='ye')

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


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

SQL> select * from stu where upper(f2)='YE';

F1 F2
---------- ----------
102 ye


Execution Plan
----------------------------------------------------------
Plan hash value: 2667645883

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

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| STU | 1 | 20 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | F2_IDX | 1 | | 1 (0)| 00:0
0:01 |

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


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

2 - access(UPPER("F2")='YE')

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


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

SQL> select * from stu where upper(f2)=upper('ye');

F1 F2
---------- ----------
102 ye


Execution Plan
----------------------------------------------------------
Plan hash value: 2667645883

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

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| STU | 1 | 20 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | F2_IDX | 1 | | 1 (0)| 00:0
0:01 |

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


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

2 - access(UPPER("F2")='YE')

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


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

SQL> set autotrace off


SQL> set pages 1000
SQL> select * from user_indexes where index_name='F2_IDX';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLE_OWNER TABLE_NAME TABLE_TYPE
------------------------------ ------------------------------ -----------
UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS
--------- -------- ------------- ------------------------------ ----------
MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
---------- -------------- ----------- ----------- ----------- ------------
PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG
------------- -------------- ---------- --------------- ---------- ---
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
---------- ----------- ------------- -----------------------
AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE
----------------------- ----------------- -------- ---------- -----------
LAST_ANAL DEGREE
--------- ----------------------------------------
INSTANCES PAR T G S BUFFER_ USE DURATION
---------------------------------------- --- - - - ------- --- ---------------
PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME
----------------- ------------------------------ ------------------------------
PARAMETERS
--------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
--- ------------ ------ -------- --- --- ---
F2_IDX FUNCTION-BASED NORMAL
ORARA STU TABLE
NONUNIQUE DISABLED USERS 2
255 65536 1 2147483645
10 YES
0 1 3 1
1 1 VALID 3 3
20-JUN-07 1
1 NO N N N DEFAULT NO


NO ENABLED NO NO NO


SQL> select * from user_ind_columns where index_name='F2_IDX';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
F2_IDX STU
SYS_NC00003$
1 10 10 ASC


SQL> select column_name from user_ind_columns where index_name='F2_IDX';

COLUMN_NAME
--------------------------------------------------------------------------------
SYS_NC00003$

SQL> desc user_ind_expressions
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_EXPRESSION LONG
COLUMN_POSITION NUMBER

SQL> select * from user_ind_expressions;

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
F2_IDX STU
UPPER("F2")
1

SQL> spool off