建立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

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

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;

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    YES
04/02/2010 17:07:50

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-631251/，如需轉載，請註明出處，否則將追究法律責任。