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

在11G以上版本中出现不可见索引，该索引默认对优化器是不可见的，但是可以通过调整optimizer_use_invisible_indexes参数并加HITI指定不可见索引，来使用该索引。

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