索引维护

索引的维护：
（1）查看索引段中extent的数量：
  SELECT   segment_name, COUNT ( * )
    FROM   dba_extents
   WHERE   segment_type = 'INDEX' AND owner = UPPER ('NEWCCS')
    GROUP BY   segment_name;
（2）查看表空间内的索引的扩展情况：
  SELECT   SUBSTR (segment_name, 1, 20) "SEGMENT NAME", bytes, COUNT (bytes)
    FROM   dba_extents
   WHERE   segment_name IN (SELECT   index_name
                            FROM   dba_indexes
                            WHERE   tablespace_name = UPPER ('NEWCCS'))
GROUP BY   segment_name, bytes
ORDER BY   segment_name

索引碎片的整理：
（1）利用验证索引命令对索引进行验证。
这将有价值的索引信息填入index_stats表。
validate index 用户名.索引名
或者：
analyze index index_name validate structure;
注意：index_stats只保存最近一次分析的结果
（2）查询index_stats表以确定索引中删除的、未填满的叶子（Leaf）行的百分比 和 height 字段。
select name,height, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent"

from index_stats
（3）如果索引的叶子行的碎片超过10%，或者 index_stats中height > =4, 可以考虑对索引进行重建。
alter index 用户名.索引名 rebuild  tablespace 表空间名  storage(initial 初始值 next 扩展值)  nologging
（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。
 alter index用户名.索引名 coalesce
（5）清除分析信息
 analyze index 用户名.索引名 delete statistics
（6） 查看索引扩展次数大于10的索引
  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

查看索引占用空间大小：
select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'INDBILLLOG5_CALLEND';
查看表占用空间大小
select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'TBILLLOG5';

 

如何加快建 index 索引 的时间:

一． 先来看一下创建索引要做哪些操作：
1. 把index key的data 读到内存
==>如果data 没在db_cache 中，这时候很容易有大量的db file scatter read wait

2. 对index key的data 作排序
==>sort_area_size 或者pga_aggregate_target 不够大的情况下，需要做 disk sort, 会有大量的driect path read/write , 另外，消耗大量CPU Time

3. 创建新的index segment , 把排过序的index data 写到所创建的index segment 里面
==>如果index 很大，那么，有时也会有redo log 相关等待，如：
log buffer space ,log file sync , log file parallel write 等

所以，在建大表索引时，可以增大pga，增大temp tablepace,并且用nologging或并行选项。

如：
create index idx_logs on logs(time) nologging parallel 4;

并行度一般看CPU 个数。当然在CPU 比较空闲的情况下可以多并行几个。对于单CPU 不建议用并行，这样反而会增加创建时间。也可以根据v$session_wait 的资料，做针对性的tuning , 这样可以降低点时间。

补充知识：

查看cpu 信息：more /proc/cpuinfo

查看内存信息：more /proc/meminfo

查看操作系统信息：more /etc/issue

 

测试实例：

SQL> select count(*) from 望江;

  COUNT(*)
----------
     49938

SQL> select index_name,index_type
  2  from user_indexes
  3  where table_name='望江';

未选定行

SQL> set timing on
SQL>
SQL> create index ix_tt on 望江(object_id);

索引已创建。

已用时间:  00: 00: 00.54
SQL> drop index ix_tt;

索引已删除。

已用时间:  00: 00: 00.07
SQL> create index ix_tt on 望江(object_id) nologging;

索引已创建。

已用时间:  00: 00: 00.23
SQL> drop index ix_tt;

索引已删除。

已用时间:  00: 00: 00.03
SQL> create index ix_tt on 望江(object_id) nologging parallel 2;

索引已创建。

已用时间:  00: 00: 00.50
SQL> drop index ix_tt;

索引已删除。

已用时间:  00: 00: 00.01
SQL> create index ix_tt on 望江(object_id)  parallel 2;

索引已创建。

已用时间:  00: 00: 00.56

看来在单CPU上，并行效果还不好. 。 但是并行在单CPU上效果不明显，而且比光使用NOLOGGING还要慢，因为出现资源争用了，可能是CPU的争用，也可能是I/O的争用。