Oracle PL/SQL之函數索引（Function-based indexes）使用樣本

函數索引（Function-based indexes）只有在where條件使用了與索引中相同的基於相同列的函數時才起作用。 duzz$scott@orcl>set autotrace on<br />duzz$scott@orcl>create table t1 as select * from dept;</p><p>Table created.</p><p>Elapsed: 00:00:00.01<br />duzz$scott@orcl>create index loc_idx on t1(upper(loc));</p><p>Index created.</p><p>Elapsed: 00:00:00.06<br />duzz$scott@orcl>select * from t1 where deptno=20;</p><p> DEPTNO DNAME LOC<br />---------- ------------------------------------------ ------------<br /> 20 RESEARCH DALLAS</p><p>Elapsed: 00:00:00.00</p><p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3617692013</p><p>--------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p><p>Predicate Information (identified by operation id):<br />---------------------------------------------------</p><p> 1 - filter("DEPTNO"=20)</p><p>Note<br />-----<br /> - dynamic sampling used for this statement</p><p>Statistics<br />----------------------------------------------------------<br /> 48 recursive calls<br /> 0 db block gets<br /> 12 consistent gets<br /> 0 physical reads<br /> 0 redo size<br /> 533 bytes sent via SQL*Net to client<br /> 385 bytes received via SQL*Net from client<br /> 2 SQL*Net roundtrips to/from client<br /> 0 sorts (memory)<br /> 0 sorts (disk)<br /> 1 rows processed</p><p>duzz$scott@orcl>select * from t1 where loc='DALLAS';</p><p> DEPTNO DNAME LOC<br />---------- ------------------------------------------ -----------------<br /> 20 RESEARCH DALLAS</p><p>Elapsed: 00:00:00.00</p><p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3617692013</p><p>--------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p><p>Predicate Information (identified by operation id):<br />---------------------------------------------------</p><p> 1 - filter("LOC"='DALLAS')</p><p>Note<br />-----<br /> - dynamic sampling used for this statement</p><p>Statistics<br />----------------------------------------------------------<br /> 5 recursive calls<br /> 0 db block gets<br /> 8 consistent gets<br /> 0 physical reads<br /> 0 redo size<br /> 533 bytes sent via SQL*Net to client<br /> 385 bytes received via SQL*Net from client<br /> 2 SQL*Net roundtrips to/from client<br /> 0 sorts (memory)<br /> 0 sorts (disk)<br /> 1 rows processed</p><p>duzz$scott@orcl>select * from t1 where upper(loc)='DALLAS';</p><p> DEPTNO DNAME LOC<br />---------- ------------------------------------------ ----------------------<br /> 20 RESEARCH DALLAS</p><p>Elapsed: 00:00:00.01</p><p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3763008475</p><p>---------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |<br />| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |<br />|* 2 | INDEX RANGE SCAN | LOC_IDX | 1 | | 1 (0)| 00:00:01 |<br />---------------------------------------------------------------------------------------</p><p>Predicate Information (identified by operation id):<br />---------------------------------------------------</p><p> 2 - access(UPPER("LOC")='DALLAS')</p><p>Note<br />-----<br /> - dynamic sampling used for this statement</p><p>Statistics<br />----------------------------------------------------------<br /> 28 recursive calls<br /> 0 db block gets<br /> 9 consistent gets<br /> 0 physical reads<br /> 0 redo size<br /> 533 bytes sent via SQL*Net to client<br /> 385 bytes received via SQL*Net from client<br /> 2 SQL*Net roundtrips to/from client<br /> 0 sorts (memory)<br /> 0 sorts (disk)<br /> 1 rows processed</p><p>duzz$scott@orcl>

REF：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm