创建隐藏索引

Creating an Invisible Index

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

To create an invisible index: 

• Use the CREATE INDEX statement with the INVISIBLE keyword.

  The following statement creates an invisible index named emp_ename for the ename column of the emp table:

  CREATE INDEX emp_ename ON emp(ename)

        TABLESPACE users

        STORAGE (INITIAL 20K

        NEXT 20k) INVISIBLE;

隐藏索引

scott@TESTDB> create index emp_ename_i on emp(ename)  invisible;

 

Index created.

 

 

scott@TESTDB> select index_name,VISIBILITY from user_indexes; 

 

INDEX_NAME           VISIBILIT

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

PK_EMP               VISIBLE

EMP_SAL_F            VISIBLE

EMP_COMM_I           VISIBLE

EMP_ENAME_I           INVISIBLE

PK_DEPT              VISIBLE

 

scott@TESTDB> select * from emp where ename='KING';

 

 

没有走索引

 

切换到系统用户，修改参数

sys@TESTDB> alter session set  optimizer_use_invisible_indexes=true;

 

Session altered.

sys@TESTDB> select * from scott.emp where ename='KING';

 

 

 

隐藏索引变正常索引或反之

sys@TESTDB> alter index scott.emp_ename_i visible;

 

Index altered.

 

scott@TESTDB>  select index_name,VISIBILITY from user_indexes;

 

INDEX_NAME                     VISIBILIT

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

PK_EMP                         VISIBLE

EMP_SAL_F                      VISIBLE

EMP_COMM_I                     VISIBLE

EMP_ENAME_I                    VISIBLE

PK_DEPT                        VISIBLE

 

 

多个索引，把慢的索引隐藏点，让他走快的索引

 

 

scott@TESTDB> alter index emp_ename_i visible;

 

Index altered.

 

scott@TESTDB> alter index emp_ename_i invisible;

 

Index altered.