创建数据：
SQL> create table tb_function
2 (
3 id integer primary key,
4 user_name varchar2(20) not null
5 );
表已创建。
SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO tb_function (id, user_name) VALUES (I, I || 'abc');
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL 过程已成功完成。
查询，查看执行计划：
SQL> set autotrace traceonly
SQL> set linesize 120
SQL> select * from tb_function where id = 1000;
执行计划
----------------------------------------------------------
Plan hash value: 3943311915
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_FUNCTION | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005718 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
377 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
但是对于下面的SQL，就不能用到索引了：
SQL> select * from tb_function where to_char(id) = '1000';
执行计划
----------------------------------------------------------
Plan hash value: 3870335670
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 9 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_FUNCTION | 1 | 25 | 9 (12)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("ID")='1000')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
469 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
上面是全表扫描，对此，可以采用基于函数的索引：
SQL> create index idx_function_fb_id on tb_function(to_char(id));
索引已创建。
SQL> select * from tb_function where to_char(id) = '1000';
执行计划
----------------------------------------------------------
Plan hash value: 3448699231
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_FUNCTION | 1 | 25 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FUNCTION_FB_ID | 40 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TO_CHAR("ID")='1000')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
38 consistent gets
4 physical reads
0 redo size
469 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
