Oracle 索引的失效和重建

2024-08-28 13:34:52

查询指定表的索引

SELECT T1.TABLE_NAME,

T1.INDEX_NAME,

T1.INDEX_TYPE,

T1.UNIQUENESS,

T1.TABLE_OWNER,

T1.STATUS,

T1.FUNCIDX_STATUS

FROM ALL_INDEXES T1

WHERE T1.TABLE_OWNER = UPPER('&Owner')

AND T1.TABLE_NAME = UPPER('&Table_Name')

ORDER BY T1.STATUS DESC;

普通索引失效,主键和唯一性索引除外(为保障数据的规范性)

SELECT 'ALTER INDEX ' || TABLE_OWNER || '.' || INDEX_NAME || ' UNUSABLE;' UNUSABLE_INDEX

FROM ALL_INDEXES

WHERE TABLE_OWNER = UPPER('&Owner')

AND TABLE_NAME = UPPER('&Table_Name')

AND INDEX_TYPE = 'NORMAL'

AND UNIQUENESS <> 'UNIQUE';

并行(PARALLEL)重建索引，提升重建速度(在服务器空闲时执行)

SELECT 'ALTER INDEX ' || TABLE_OWNER || '.' || INDEX_NAME ||

' REBUILD PARALLEL;'

FROM ALL_INDEXES

WHERE TABLE_OWNER = UPPER('&Owner')

AND TABLE_NAME = UPPER('&Table_Name')

AND INDEX_TYPE = 'NORMAL'

AND UNIQUENESS <> 'UNIQUE'

AND STATUS = 'UNUSABLE';

查询索引的状态和DEGREE并行度

注意：并行度默认设置为1，如果显示为default，表示数据库分配的cpu核心*2,重建完索引后并行度都改成1

SELECT ROWNUM,

S.INDEX_NAME,

S.INDEX_TYPE,

S.TABLE_OWNER,

S.TABLE_NAME,

S.UNIQUENESS,

S.STATUS,

S.DEGREE,

S.TABLESPACE_NAME

FROM ALL_INDEXES S

WHERE TABLE_OWNER = UPPER('&Owner')

AND TABLE_NAME = UPPER('&Table_Name');

将索引并行度不是1的还原回1

SELECT 'ALTER INDEX ' || S.TABLE_OWNER || '.' || S.INDEX_NAME ||

' NOPARALLEL;'

FROM ALL_INDEXES S

WHERE TABLE_OWNER = UPPER('&Owner')

AND TABLE_NAME = UPPER('&Table_Name')

AND S.DEGREE <> 1;

最新文章

热门文章