熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

11G的不可見索引

2013-11-13 15:58:41  來源: Oracle 

  不可見索引是G的新特性不可見並非物理上看不見其實還是存在的ORACLE

  還會進行維護只是ORACLE並不會使用它 這在維護索引的時候非常有用譬如可以把

  這個索引標志為不可見看看系統的性能如何如果沒人使用到索引可以DROP掉了如果有人要用到這個索引可以在標記為可見省去了假設性能不好在重建對系統的影響

  SQL> conn TEST/test

  Connected

  SQL> desc t

  Name                                      Null?    Type

  

  ID                                                 NUMBER()

  NAME                                               VARCHAR()

  SQL> create index idx_t_ on t(id) invisible;

  Index created

  SQL> exec dbms_statsgather_table_stats(usertcascade=>true);

  PL/SQL procedure successfully completed

  SQL> select index_namevisibility from user_indexes;

  INDEX_NAME                     VISIBILIT

  

  IDX_T_                       INVISIBLE

  SQL> set autot traceonly exp

  SQL> set linesize

  SQL> select * from t where id=;

  Execution Plan

  

  Plan hash value:

  

  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

  

  |   | SELECT STATEMENT  |      |     |    |       ()| :: |

  |*  |  TABLE ACCESS FULL| T    |     |    |       ()| :: |

  

  Predicate Information (identified by operation id):

  

   filter(ID=)

  SQL> set autot off

  SQL> alter index idx_t_ visible;

  Index altered

  SQL> select index_namevisibility from user_indexes;

  INDEX_NAME                     VISIBILIT

  

  IDX_T_                       VISIBLE

  SQL> set autot traceonly exp

  SQL> select * from t where id=;

  Execution Plan

  

  Plan hash value:

  

  | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

  

  |   | SELECT STATEMENT            |          |     |    |        ()| :: |

  |   |  TABLE ACCESS BY INDEX ROWID| T        |     |    |        ()| :: |

  |*  |   INDEX RANGE SCAN          | IDX_T_ |     |       |        ()| :: |

  

  Predicate Information (identified by operation id):

  

   access(ID=)

  SQL>


From:http://tw.wingwit.com/Article/program/Oracle/201311/17616.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.