visible invisible索引

最新推荐文章于 2024-09-13 14:18:05 发布

EVISWANG

最新推荐文章于 2024-09-13 14:18:05 发布

阅读量503

点赞数

CC 4.0 BY-SA版权

分类专栏： Index

本文链接：https://blog.youkuaiyun.com/EVISWANG/article/details/50668975

Index 专栏收录该内容

12 篇文章

订阅专栏

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			   1
EMP	     EMP_ID1	     EMPNO			   1
EMP	     EMP_ID1	     ENAME			   2
EMP	     EMP_ID1	     DEPTNO			   3
EMP	     EMP_ID2	     SAL			   1
EMP	     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(*)
----------
	 1


Execution 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 size
	526  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

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

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(*)
----------
	 1


Execution 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 size
	526  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

修改为可视就ok了

alter index dept_rich_inv_idx visible;

SQL> select count(*) from dept_rich where deptno=30;

  COUNT(*)
----------
	 1


Execution 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(*)
----------
	 1


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