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