数据库索引（Oracle ）

索引的建立一般规则

1. 单表索引不超过6个。
2. 每个索引不超过3个字段。
3. 索引匹配时，可以包含关系，但脚本中的字段在索引的前面的连续字段时，正常索引，否则会变成skip索引（索引性能下降70%)，如SQL条件里有C1，C3字段，如果索引是C1， C2，C3，则是 Skip index，如果是C1,C3,C2，则是正常索引。
4. 唯一性索引有可能会影响到查询性能，主要是统计信息处理导致脚本不去用更优化的索引，而是用唯一索引。可以考虑用采隐藏索引技术。
5. 脚本实现的时候，索引可选择性高的条件放在条件语句的前面，可以提高脚本性能。
6. 索引单独表空间，存储在一起时，效果不大，需要在不同的存储设备上。
7. 函数索引（TO_DATE)并不能提升效率，要从索引使用上分析。
8. 可以根据历史统计信息查找索引使用的次数，并分析索引合理性，可以监控索引使用情况（会影响性能）。
9.  查索引选择性：

select a.column_name,   
    b.num_rows,    
    a.num_distinct Cardinality,  
    round(a.num_distinct / b.num_rows * 100, 2) selectivity   
from dba_tab_col_statistics a, dba_tables b    
where a.owner = b.owner      
and a.table_name = b.table_name      
and a.owner = upper('XIR_TRD')      
and a.table_name = upper('TTRD_ACCOUNTING_SECU_OBJ_HIS')   
and a.column_name = upper('TSK_ID');

 

 10. 统计信息收集与索引重建统计信息自动收集，数据量变化超过 20%自动，定时收集。

--收集单表的统计信息

call dbms_stats.gather_table_stats(user, '表名');

 

--收集整个schema的统计信息

call dbms_stats.gather_schema_stats(user); 

--普通索引重建

SELECT 'alter index ' || INDEX_NAME || ' rebuilding on line

;', T.*  FROM   USER_INDEXES T

WHERE  T.TEMPORARY = 'N' AND

       INDEX_TYPE = 'NORMAL' AND

       T.PARTITIONED = 'NO';  

 

11. 如何判断索引是否使用

SELECT *

FROM   DBA_INDEXES

WHERE  (OWNER, INDEX_NAME) NOT IN

       (SELECT OBJECT_OWNER, OBJECT_NAME

        FROM   DBA_HIST_SQL_PLAN) ;

 

12. 隐藏索引

Alter index 索引名  invisible / visible;

 

13. 表的高水位

分配的表空间

SELECT segment_name, segment_type, blocks  FROM dba_segments

WHERE segment_name=’TTRD_ACCOUNTING_SECU_OBJ_HIS’;

使用表空间   

SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name=' TTRD_ACCOUNTING_SECU_OBJ_HIS '

当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用 delete 语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低。Select 语句扫描的数据块增加。    降低方法：

   Truncate    执行表重建指令 alter table table_name move;

  

 alter table table_name shrink space;

   重建表    导出/导入表

   alter   table  table_name  deallocate   unused;