关于oracle 11g中的索引.
如果我们一个表在设计上存在问题如: 当某个索引在当初设计时就存在着缺陷或者不合理时,现在已经产生了几千条几万条时,我们不能直接把他删除掉, 在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 VISIBLE PK_EMP VISIBLE EMP_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(*) ---------- 1 Execution 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 VISIBLE PK_EMP VISIBLE EMP_ENAME INVISIBLE SQL> alter index EMP_ENAME rebuild; =Index altered. SQL> select INDEX_NAME ,VISIBILITY from user_indexes; INDEX_NAME VISIBILITY ------------------------------------------------------------ ------------------ PK_DEPT VISIBLEPK_EMP VISIBLE EMP_ENAME VISIBLESQL>