LXTB INDX 1 44 DEFAULT
#切换缓存池 SQL> alter index scott.pk_emp storage(buffer_pool keep);
Index altered.
SQL> col index_name for a20 SQL> col index_type for a10 SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks,buffer_pool 2 from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE BLEVEL LEAF_BLOCKS BUFFER_ -------------------- ---------- ---------- ---------- ---------- ----------- ------- I_NAME NORMAL LXTB INDX 1 60 DEFAULT I_GENDER NORMAL LXTB USERS 1 37 DEFAULT I_UPPER FUNCTION-B LXTB INDX 1 60 DEFAULT ASED NORMA L
I_ID NORMAL/REV LXTB INDX 1 44 DEFAULT
SQL> select object_id,object_name,object_type from dba_objects where owner='SCOTT';
OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- -------------------- 10184 DEPT TABLE 10185 PK_DEPT INDEX 10186 EMP TABLE 10187 PK_EMP INDEX 10188 BONUS TABLE 10189 SALGRADE TABLE
6 rows selected.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024 k,extents,blocks 2 from dba_segments where owner='SCOTT';
SEGMENT_NA SEGMENT_TY TABLESPACE K EXTENTS BLOCKS ---------- ---------- ---------- ---------- ---------- ---------- DEPT TABLE USERS 64 1 8 PK_DEPT INDEX USERS 64 1 8 EMP TABLE USERS 64 1 8 PK_EMP INDEX USERS 64 1 8 BONUS TABLE USERS 64 1 8 SALGRADE TABLE USERS 64 1 8
SQL> select constraint_name,table_name,column_name 2 from dba_cons_columns where owner='SCOTT';
CONSTRAINT TABLE_NAME COLUMN_NAM ---------- ---------- ---------- PK_DEPT DEPT DEPTNO PK_EMP EMP EMPNO FK_DEPTNO EMP DEPTNO
以下内容参考：http://blog.csdn.net/rlhua/article/details/13776423
十一.设置index 为invisible.
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.