频道栏目
首页 > 数据库 > Oracle > 正文
如何让ORACLE索引不可见
2011-05-30 11:00:23           
收藏   我要投稿

我们经常在数据库上建索引或删除索引,由于索引对SQL的执行性能影响非常大,有可能变得很好,也有可能变得很差,在线下开发环境我们可以充分测试,对于创建或删除索引没什么问题。但是在线上环境,由于高并发的访问,如果我们删除了一个重要的大索引(GB以上),删除后才发现大量SQL性能变差,很快主机就LOAD飙升,系统无法运行了,由于索引已经删除,并且很大,要当场重建基本不可能,因为这个索引巨大,创建估计要几分钟甚至几个小时,况且这时主机已经基本没有响应,IO全部用光,只能把应用停了,等索引建好后再开始打开应用,等发生这样的事才会为自己的失误而后悔。那我们有没有办法让删除索引的风险降低呢,请看下文:

我们先创建一个表t1,在t1的table_name字段上新建一个索引idx_t1_table_name。

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation            |  Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |     1 |    17 |     2  (50)|
|   1 |  SORT AGGREGATE      |                    |     1 |    17 |            |
|*  2 |   INDEX RANGE SCAN   | IDX_T1_TABLE_NAME  |     2 |    34 |     2  (50)|
--------------------------------------------------------------------------------

OK,从上面我们可以看到索引很正常。那现在假设我们不再需要这个索引了,因此想删除它,但又不知道会不会有本文开头所说的风险。如果可以删除前先把索引不可见,确认没问题后再删除,这样就没问题了,为此,Oracle11g推出新的功能,设置索引是否可见,示例如下:

SQL> alter index IDX_T1_TABLE_NAME invisible;
 
Index altered
 
SQL>  explain plan for select count(*) from t1 where table_name=DUAL;
 
Explained
 
SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    17 |    23  (22)|
|   1 |  SORT AGGREGATE      |             |     1 |    17 |            |
|*  2 |   TABLE ACCESS FULL  | T1          |     2 |    34 |    23  (22)|
-------------------------------------------------------------------------

这样索引就隐藏起来了,如果把索引隐藏后发现有性能问题,我们可以立即打开索引:
SQL> alter index IDX_T1_TABLE_NAME visible;
 
Index altered
 
SQL> explain plan for select count(*) from t1 where table_name=T1;
 
Explained
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3098159
--------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    17 |     1   (0)| 00:
|   1 |  SORT AGGREGATE   |                   |     1 |    17 |            |
|*  2 |   INDEX RANGE SCAN| IDX_T1_TABLE_NAME |     1 |    17 |     1   (0)| 00:
--------------------------------------------------------------------------------

 
 

      以上是Oracle11g的处理方法,但是在Oracle9i或Oracle10g中索引没有invisible的功能,我们如何处理呢?
现在Oracle数据库一般都采用基于成本的计算方法来生成执行计划,只要索引的成本更低,ORACLE就会选择使用索引,OK,那我们只要告诉ORACLE使用这个索引成本很高,它就不会使用这个索引,这样就达到了暂时让索引不可用的效果。相信很多人都知道ORACLE提供了dbms_stats包来管理对像的统计信息,通过dbms_stats.set_index_stats函数我们可以强制设置统计信息,现在我们只要把索引的成本设置成非常大即可,如下所示:

--查看IDX_T1_TABLE_NAME的基本统计信息
SQL> select a.owner,a.index_name,a.blevel,a.leaf_blocks,a.num_rows from all_indexes a where owner=YZS and index_name=IDX_T1_TABLE_NAME;
 
OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ----------
YZS                            IDX_T1_TABLE_NAME                       1          12       2710

--设置非常离谱的统计信息,让ORACLE认为使用索引的成本很高
SQL> exec dbms_stats.set_index_stats(ownname => YZS,indname => IDX_T1_TABLE_NAME,indlevel => 10,numlblks => 1000000000,numrows => 100000000000,no_invalidate => false );
 
PL/SQL procedure successfully completed

备注:indlevel值不能设置太高,有些版本的ORACLE有BUG,会导致SQL解析发生错误。numlblks的值也不用太大,因为ORACLE统计信息里索引的块数最高也只能是4294967295。 no_invalidate=false表示让CACHE中的执行计划立即失效,重新按现在的统计信息生成SQL执行计划。

--验证一下是否生效
SQL> explain plan for select count(*) from t1 where table_name=DUAL;
 
Explained
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time   &nbs

点击复制链接 与好友分享!回本站首页
相关TAG标签 索引
上一篇:Oracle expdp/impdp用法
下一篇:Oracle ASM的管理(二)
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站