看了和B树索引有关的那几篇，得到些实用而以前不知道的东西，但觉得还可以再深入些。索引嘛，重点还是在应用。

这些代码比较有用，看完再加。

近期索引使用次数

select object_name,operation,options,count(1)

 from dba_hist_sql_plan d,dba_hist_sqlstat h where d.object_owner<>'SYS'

and d.operation like '%INDEX%'

and d.sql_id=h.sql_id

group by d.object_name,d.operation,d.options

order by 1,2,3;

对表的访问中索引使用比例（自己想的）

select object_name,object_type,STATISTIC_NAME,value from v$segment_statistics

where statistic_name in ('logical reads')

and object_name in

('IDX_YL_YLDCK_EQDYJH','YL_YLDCK','PK_YL_YLDCK');

索引是否被用

alter index myindex monitoring/nomonitoring usage

select index_name,monitoring,used from v$object_usage;

索引大小

select a.segment_name,a.segment_type,a.extents,a.BYTES from user_segments a , user_indexes b where

a.segment_name=b.index_name

and b.table_owner='HBCMES'

扩展多次的索引需要重建

select * from index_stats;

SELECT COUNT(*), owner, segment_name, tablespace_name

  FROM dba_extents

 WHERE segment_type = 'INDEX'

   AND owner NOT IN ('SYS', 'SYSTEM')

 GROUP BY owner, segment_name, tablespace_name

HAVING COUNT(*) > 10

 ORDER BY COUNT(*) DESC

#

用index_stats确定索引是否需要重建

analyze myindex validate structure

select * from index_stats

del_le_rows说明碎片多

评估重建索引的大小

SELECT owner "OWNER",

       segment_name "INDEX",

       tablespace_name "TABLESPACE",

       bytes "BYTES/COUNT",

       SUM(bytes) "TOTAL BYTES",

       ROUND(SUM(bytes) / (1024 * 1024), 0) "TOTAL M",

       COUNT(bytes) "TOTAL COUNT"

  FROM dba_extents

 WHERE segment_type = 'INDEX'

   AND segment_name IN ('IDX_CPSJ_DQGX', 'IDX_CPSJ_RKRQ')

 GROUP BY owner, segment_name, segment_type, tablespace_name, bytes

 ORDER BY owner, segment_name