visible invisible索引

SQL> show user
USER is "SCOTT"
SQL> create  index emp_id1 on emp(empno, ename, deptno);
SQL> create index emp_id2 on emp (sal);

查看创建索引信息：

SQL> select table_name, index_name from user_indexes where table_name='EMP';
TABLE_NAME INDEX_NAME
--------------- ------------------------------------------------------------------------------------------
EMP EMP_ID2
EMP EMP_ID1
EMP PK_EMP
SQL>

查看每个索引中包含的列：

  col table_name for  a12
  col index_name for a15
  col column_name for a15

select table_name, index_name, column_name, column_position from user_ind_columns
order by table_name, index_name, column_position;

TABLE_NAME   INDEX_NAME      COLUMN_NAME     COLUMN_POSITION------------ --------------- --------------- ---------------DEPT     PK_DEPT     DEPTNO   1EMP     EMP_ID1     EMPNO   1EMP     EMP_ID1     ENAME   2EMP     EMP_ID1     DEPTNO   3EMP     EMP_ID2     SAL   1EMP     PK_EMP     EMPNO   1

注：dba_indexex和dba_ind_columns可以检索到一个给定的表的索引列表，对于当前用户方案的索引信息，只能

       使用user_indexes和user_ind_columns来查看。

索引可视（不可视）

create table dept_rich as select * from dept;

create index dept_rich_inv_idx on dept_rich(deptno) invisible;

SQL> select count(*) from dept_rich where deptno=30;  COUNT(*)---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3024595593--------------------------------------------------------------------------------| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |       |     1 |    13 |     3 (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |       |     1 |    13 |    |       ||*  2 |   TABLE ACCESS FULL| DEPT_RICH |     1 |    13 |     3 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("DEPTNO"=30)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------  5  recursive calls  0  db block gets  7  consistent gets  0  physical reads  0  redo size526  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processedSQL> 

但是可以使用提示强制使用这个索引

select /*+use_invisible_indexes*/ count(*) from dept_rich
   where deptno=30;

SQL> select /*+use_invisible_indexes*/ count(*) from dept_rich   where deptno=30;  2    COUNT(*)---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3699452051---------------------------------------------------------------------------------------| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    13 |     1(0)| 00:00:01 ||   1 |  SORT AGGREGATE   |      |     1 |    13 |    |      ||*  2 |   INDEX RANGE SCAN| DEPT_RICH_INV_IDX |     1 |    13 |     1(0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("DEPTNO"=30)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------  7  recursive calls  0  db block gets  8  consistent gets  0  physical reads  0  redo size526  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processedSQL> 

修改为可视就ok了

alter index dept_rich_inv_idx visible;

SQL> select count(*) from dept_rich where deptno=30;  COUNT(*)---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3699452051---------------------------------------------------------------------------------------| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    13 |     1(0)| 00:00:01 ||   1 |  SORT AGGREGATE   |      |     1 |    13 |    |      ||*  2 |   INDEX RANGE SCAN| DEPT_RICH_INV_IDX |     1 |    13 |     1(0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------

当然在索引visible的状态下也可以使用no_index提示来关闭一个索引

SQL> select /*+no_index(dept_rich dept_rich_inv_idx) */ count (*) from dept_rich where deptno =30;  COUNT(*)---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3024595593--------------------------------------------------------------------------------| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |       |     1 |    13 |     3 (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |       |     1 |    13 |    |       ||*  2 |   TABLE ACCESS FULL| DEPT_RICH |     1 |    13 |     3 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("DEPTNO"=30)Note-----   - dynamic sampling used for this statement (level=2)