如何让Oracle索引不起作用(不可见)


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

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

  1. create table t1 as select * from all_tables;--创建表  
  2. create index idx_t1_table_name on t1(table_name); --创建索引  
  3. analyze table t1 compute statistics; --收集表的统计信息  
  4.    
  5. 用执行计划验证索引是否有效  
  6. SQL> explain plan for select count(*) from t1 where table_name='DUAL';   
  7.    
  8. Explained  
  9.    
  10. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
  11.    
  12. PLAN_TABLE_OUTPUT  
  13. --------------------------------------------------------------------------------  
  14. --------------------------------------------------------------------------------  
  15. | Id  | Operation            |  Name              | Rows  | Bytes | Cost (%CPU)|  
  16. --------------------------------------------------------------------------------  
  17. |   0 | SELECT STATEMENT     |                    |     1 |    17 |     2  (50)|  
  18. |   1 |  SORT AGGREGATE      |                    |     1 |    17 |            |  
  19. |*  2 |   INDEX RANGE SCAN   | IDX_T1_TABLE_NAME  |     2 |    34 |     2  (50)|  
  20. --------------------------------------------------------------------------------  

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

  1. SQL> alter index IDX_T1_TABLE_NAME invisible;  
  2.    
  3. Index altered  
  4.    
  5. SQL>  explain plan for select count(*) from t1 where table_name='DUAL';   
  6.    
  7. Explained  
  8.    
  9. SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
  10.    
  11. PLAN_TABLE_OUTPUT  
  12. -------------------------------------------------------------------------  
  13. | Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|  
  14. -------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT     |             |     1 |    17 |    23  (22)|  
  16. |   1 |  SORT AGGREGATE      |             |     1 |    17 |            |  
  17. |*  2 |   TABLE ACCESS FULL  | T1          |     2 |    34 |    23  (22)|  
  18. -------------------------------------------------------------------------  
  19.   
  20. 这样索引就隐藏起来了，如果把索引隐藏后发现有性能问题，我们可以立即打开索引：  
  21. SQL> alter index IDX_T1_TABLE_NAME visible;  
  22.    
  23. Index altered  
  24.    
  25. SQL> explain plan for select count(*) from t1 where table_name='T1';   
  26.    
  27. Explained  
  28.    
  29. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
  30.    
  31. PLAN_TABLE_OUTPUT  
  32. --------------------------------------------------------------------------------  
  33. Plan hash value: 3098159  
  34. --------------------------------------------------------------------------------  
  35. | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim  
  36. --------------------------------------------------------------------------------  
  37. |   0 | SELECT STATEMENT  |                   |     1 |    17 |     1   (0)| 00:  
  38. |   1 |  SORT AGGREGATE   |                   |     1 |    17 |            |  
  39. |*  2 |   INDEX RANGE SCAN| IDX_T1_TABLE_NAME |     1 |    17 |     1   (0)| 00:  
  40. --------------------------------------------------------------------------------  
  41.   
  42.    

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

  1. --查看IDX_T1_TABLE_NAME的基本统计信息  
  2. 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';   
  3.    
  4. OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS  
  5. ------------------------------ ------------------------------ ---------- ----------- ----------  
  6. YZS                            IDX_T1_TABLE_NAME                       1          12       2710  
  7.   
  8. --设置非常离谱的统计信息，让ORACLE认为使用索引的成本很高  
  9. SQL> exec dbms_stats.set_index_stats(ownname => 'YZS',indname => 'IDX_T1_TABLE_NAME',indlevel => 10,numlblks => 1000000000,numrows => 100000000000,no_invalidate => false );   
  10.    
  11. PL/SQL procedure successfully completed  
  12.   
  13. 备注：indlevel值不能设置太高，有些版本的ORACLE有BUG，会导致SQL解析发生错误。numlblks的值也不用太大，因为ORACLE统计信息里索引的块数最高也只能是4294967295。 no_invalidate=false表示让CACHE中的执行计划立即失效，重新按现在的统计信息生成SQL执行计划。  
  14.   
  15. --验证一下是否生效  
  16. SQL> explain plan for select count(*) from t1 where table_name='DUAL';   
  17.    
  18. Explained  
  19.    
  20. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
  21.    
  22. PLAN_TABLE_OUTPUT  
  23. --------------------------------------------------------------------------------  
  24. Plan hash value: 3724264953  
  25. ---------------------------------------------------------------------------  
  26. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  27. ---------------------------------------------------------------------------  
  28. |   0 | SELECT STATEMENT   |      |     1 |    19 |    30   (0)| 00:00:01 |  
  29. |   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          |  
  30. |*  2 |   TABLE ACCESS FULL| T1   |     1 |    19 |    30   (0)| 00:00:01 |  
  31. ---------------------------------------------------------------------------  
  32.   
  33. OK，现在已经看到了，没有使用索引，那如果我们发现修改统计信息后一些SQL性能变差，我们可以把索引的真实的统计信息还原即可，如下所示：  
  34.   
  35. SQL> exec dbms_stats.set_index_stats(ownname => 'YZS',indname => 'IDX_T1_TABLE_NAME',indlevel => 1,numlblks => 12,numrows => 2710,no_invalidate => false );   
  36.    
  37. PL/SQL procedure successfully completed  
  38.    
  39. SQL> explain plan for select count(*) from t1 where table_name='DUAL';   
  40.    
  41. Explained  
  42.    
  43. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
  44.    
  45. PLAN_TABLE_OUTPUT  
  46. --------------------------------------------------------------------------------  
  47. Plan hash value: 3098159  
  48. --------------------------------------------------------------------------------  
  49. | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim  
  50. --------------------------------------------------------------------------------  
  51. |   0 | SELECT STATEMENT  |                   |     1 |    19 |     1   (0)| 00:  
  52. |   1 |  SORT AGGREGATE   |                   |     1 |    19 |            |  
  53. |*  2 |   INDEX RANGE SCAN| IDX_T1_TABLE_NAME |     1 |    19 |     1   (0)| 00:  
  54. --------------------------------------------------------------------------------  
  55.   
  56. 这样我们就快速还原回开始时的状态，不会再发生悲剧了。  

使用统计信息骗Oracle不使用索引的方法是偏门，如果是Oracle11g 建议还是直接使用invisible更好。
为了更好的进行索引管理，Oracle还提供了索引监视的功能，这样可以准确的确认索引还有没有使用。语法如下：

  1. --打开索引监视  
  2. SQL> alter index IDX_T1_TABLE_NAME monitoring usage;  
  3.    
  4. Index altered  
  5.    
  6. SQL> select * from v$object_usage where INDEX_NAME='IDX_T1_TABLE_NAME';   
  7.    
  8. INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING  
  9. ------------------------------ ------------------------------ ---------- ---- ------------------- -------------------  
  10. IDX_T1_TABLE_NAME              T1                             YES        NO   05/28/2011 22:01:26   
  11.   
  12. --USED字段表示这个索引有没有使用过  
  13.    
  14. SQL> select count(*) from t1 where table_name='DUAL';   
  15.    
  16.   COUNT(*)  
  17. ----------  
  18.          1  
  19.    
  20. SQL> select * from v$object_usage where INDEX_NAME='IDX_T1_TABLE_NAME';   
  21.    
  22. INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING  
  23. ------------------------------ ------------------------------ ---------- ---- ------------------- -------------------  
  24. IDX_T1_TABLE_NAME              T1                             YES        YES  05/28/2011 22:01:26   
  25.    
  26.    
  27. --停止监视  
  28. SQL> alter index IDX_T1_TABLE_NAME nomonitoring usage;  
  29.    
  30. Index altered   

相关内容