关于oracle 11g中的索引.

来源：互联网发布：淘宝店经营地址怎么写编辑：程序博客网时间：2024/06/09 22:43

关于oracle 11g中的索引.

2011-01-30 10:12 443人阅读评论(0)收藏举报

oracleusersqlsessionaccesstable

目录(?)[-]

创建 INVISIBLE 索引
观察执行计划的影响

如果我们一个表在设计上存在问题如: 当某个索引在当初设计时就存在着缺陷或者不合理时,现在已经产生了几千条几万条时,我们不能直接把他删除掉, 在11g 之前我们只能先把数据转移到一个新表,然后在来删除索引,然而在11g后,oracle新增了一个功能,那就是------invisible index.

当你想要一个索引不可用,但是又不能删除,你可以这样做:

alter index index_name invisible;

把一个不可用索引修改为可用 :
alter index index_name visible;

查看索引是否可用:

SELECT INDEX_NAME, VISIBILITY
FROM USER_INDEXES where index_name = Index_name1 ;

INDEX_NAME     VISIBILITY
------------------    -------------
index_name1        VISIBLE

下面我们来看看 invisible index 对执行计划的影响.

创建 INVISIBLE 索引

注意新的关键字

SQL> CREATE INDEX emp_ename ON emp(ename)  2        TABLESPACE users  3        INVISIBLE;Index created

USER_INDEXES 视图的新列 VISIBILITY

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;INDEX_NAME                                                   VISIBILITY------------------------------------------------------------ ------------------PK_DEPT                                                      VISIBLEPK_EMP                                                       VISIBLEEMP_ENAME                                                    INVISIBLE

观察执行计划的影响

SQL> select count(*) from emp where ename='ADAMS';  COUNT(*)----------         1

Execution Plan----------------------------------------------------------Plan hash value: 2083865914---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |     7 |     3   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          ||*  2 |   TABLE ACCESS FULL| EMP  |     1 |     7 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------

告诉优化器使用 INVISIBLE 索引

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;Session altered.SQL> select count(*) from emp where ename='ADAMS';  COUNT(*)----------         1Execution Plan----------------------------------------------------------Plan hash value: 1569421590-------------------------------------------------------------------------------| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |           |     1 |     7 |     1   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |           |     1 |     7 |            |          ||*  2 |   INDEX RANGE SCAN| EMP_ENAME |     1 |     7 |     1   (0)| 00:00:01 |-------------------------------------------------------------------------------

值得注意的是 VISIBILITY 索引在 Rebuild 后会变成可见索引：

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;INDEX_NAME                                                   VISIBILITY------------------------------------------------------------ ------------------PK_DEPT                                                      VISIBLEPK_EMP                                                       VISIBLEEMP_ENAME                                                    INVISIBLESQL> alter index EMP_ENAME rebuild;=Index altered.SQL> select INDEX_NAME ,VISIBILITY from user_indexes;INDEX_NAME                                                   VISIBILITY------------------------------------------------------------ ------------------PK_DEPT                                                      VISIBLEPK_EMP                                                       VISIBLEEMP_ENAME                                                    VISIBLESQL>

0 0