索引會增加io，增加空間，也會增加一些數(shù)據(jù)庫的額外開銷，
對于沒有用到得索引，應盡量避免不建，建立了的，把沒用的索引根據(jù)情況刪除。有唯一約束，主鍵的列不要刪除。
建議一個表的所以在4-5個左右，不要太多，這是一個參考值，當然還要具體分析。
create table test03(id number(3),name varchar2(10));
insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
insert into test values(4,'d');
insert into test values(5,'e');
insert into test values(6,'f');
commit;
alter table test03 add (constraint test_pk primary key (id));
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$ob
ject_usage;
未選定行
此時未使用索引。下面開啟監(jiān)控：
alter index test_pk monitoring usage;
select * from test03 where id = 2;
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$ob
ject_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
TEST_PK YES YES 07/26/2010 15:16:35
可見已經(jīng)使用了該索引。
alter index test_pk nomonitoring usage;
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$ob
ject_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
TEST_PK NO YES 07/26/2010 15:16:35 07/26/2010 15:17:23
下面是兩個開啟整庫索引監(jiān)控的腳本：
start_index_monitor.sql
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes where owner in ('CRMII','DCUSER');
spool off
set heading on
set echo on
set feedback on
stop_index_monitor.sql
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes where owner in ('YOUR','PROD_DB','OWNER','LIST');
spool off
set heading on
set echo on
set feedback on
