在Oracle8i中，使用函数索引必须将初始化参数QUERY_REWRITE_ENABLED设置为TRUE，且QUERY_REWRITE_INTEGRITY设置为TRUSTED。但是在920中，似乎函数索引不再依赖于初始化参数的设置。

SQL> SELECT * FROM V$VERSION;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> SHOW PARAMETER OPTIMIZER_MODE

NAME                       TYPE        VALUE
-------------------------- ----------- --------------------
optimizer_mode             string      CHOOSE
SQL> SHOW PARAMETER QUERY

NAME                       TYPE        VALUE
-------------------------- ----------- --------------------
query_rewrite_enabled      string      TRUE
query_rewrite_integrity    string      enforced
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;

会话已更改。

SQL> SHOW PARAMETER QUERY

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
query_rewrite_enabled      string      FALSE
query_rewrite_integrity    string      enforced
SQL> CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE INDEX IND_TEST_NAME ON TEST (LOWER(OBJECT_NAME));

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')

PL/SQL 过程已成功完成。

SQL> COL OBJECT_NAME FORMAT A30
SQL> SET AUTOT ON EXP
SQL> SELECT OWNER, OBJECT_NAME FROM TEST WHERE LOWER(OBJECT_NAME) = 't';

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
YANGTK                         T

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Bytes=23)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=1 Card=1)

good article.