oracle的index过度滥用的治理
·
1、打开监控，删除未使用的index
SELECT 'drop INDEX GZFP."'||INDEX_NAME||'" ;' FROM V$OBJECT_USAGE where used='NO' and upper(index_name) like '%LITIDX%'
2、表索引过大，但是表存在数据同步批量insert的需要删除
select table_name,count(*) from user_indexes where index_name in (
SELECT segment_name
FROM user_extents
where segment_type='INDEX' and upper(segment_name) like '%LITIDX%'--and owner like 'JG_%'
GROUP BY segment_name
--having SUM (BYTES) / 1024 / 1024/1024 between 0.1 and 0.5
) group by table_name
order by 2 desc
3、列重复过多的index需要合并
SELECT Table_Name,
Column_Name,
COUNT(*)
FROM User_Ind_Columns
GROUP BY Table_Name,
Column_Name
having count(*)>1
order by 3 desc
更多推荐
7
0- 0
已为社区贡献5条内容
所有评论(0)