创建Function-Based Indexes-优快云博客

[oracle@localhost ~]$ sqlplus hr/hr

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 2 16:50:34 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter query_re

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
query_rewrite_enabled                string
TRUE
query_rewrite_integrity              string
enforced

创建函数索引
SQL> create index bbb on employees(upper(first_name));
Index created.

更新索引所在表统计数据

SQL> analyze table employees compute statistics;

SQL> set autotrace on
SQL> alter session set query_rewrite_enabled=true;
SQL> alter session set query_rewrite_integrity=trusted;

SQL> select * from employees where upper(first_name)='JENNIFER';

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

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

|* 1 | FILTER | | | | |
|

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 2 (0)|
00:00:01 |

|* 3 | INDEX RANGE SCAN | BBB | 1 | | 1 (0)|
00:00:01 |

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

打开索引监控

SQL> ALTER INDEX BBB MONITORING USAGE;

SQL> select * from v$object_usage;

INDEX_NAME
------------------------------------------------------------
TABLE_NAME                                                   MONITO USED
------------------------------------------------------------ ------ ------
START_MONITORING                       END_MONITORING
-------------------------------------- --------------------------------------
EMP_ID_NO
EMP                                                          NO     YES
12/21/2009 14:21:34                    04/02/2010 17:09:38

BBB
EMPLOYEES YES NO
04/02/2010 17:07:50

SQL> select * from employees where upper(first_name)='JENNIFER';

SQL> select * from v$object_usage;

BBB
EMPLOYEES YES YES
04/02/2010 17:07:50

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-631251/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-631251/