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