11G利用HINT使用不可见索引

在11G以上版本中出现不可见索引，该索引默认对优化器是不可见的，但是可以通过调整optimizer_use_invisible_indexes参数并加HINT指定不可见索引，来使用该索引。
[code]SQL> desc t_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

SQL> select count(*) from t_test;

COUNT(*)
----------
11879

SQL> create index ind_t_obj_id on t_test(object_id) invisible;

Index created.


SQL> show parameter optimizer_use_invisible_indexes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

SQL> alter system set optimizer_use_invisible_indexes=true;

System altered.

SQL> show parameter optimizer_use_invisible_indexes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean TRUE

SQL> set autotrace traceonly
SQL> select /*index (t_test ind_t_obj_id) */ object_id from t_test where object_id=1000;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 4061241246

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_T_OBJ_ID | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------- [/code]