在oracle中,重建普通表上的索引很简单。要重建特定索引,只需执行如下sql命令:
ALTER INDEX INDEX_NAME Rebuild;
这里INDEX_NAME 代表索引的名字,下同。
1.重建某个表上的全局索引,执行如下PL/SQL代码:
重建这张表上的全局索引的sql代码如下:
begin
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = 'T2')
loop
if c1.partitioned='NO' then
execute immediate 'ALTER INDEX ' || c1.index_name || ' REBUILD';
end if;
end loop;
end;
/
这里,TABLE_NAME 代表索引的名字,下同。
2.而重建分区表上的索引:
2.1如果这个索引是分区的,重建所有分区方法:
begin
for c2 in (select partition_name from user_ind_partitions where index_name='INDEX_NAME' and status = 'UNUSABLE')
loop
execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name;
end loop;
end;
/
2.2如果这个索引是非分区的,重建所有非分区索引方法: (和普通表上的相同)
begin
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = 'TABLE_NAME')
loop
if c1.partitioned='YES'
-- rebuild every unusable partition for partitioned index
for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE')
loop
execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name;
end loop;
end if;
end loop;
end;
3.重建这张表上的全部索引(全局和分区):
begin
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = 'T2'))
loop
if c1.partitioned='NO' then
-- rebuild global index directly重建全局索引
execute immediate 'alter index ' || c1.index_name || ' rebuild';
else
-- rebuild every unusable partition for partitioned index 重建分区索引
for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE')
loop
execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name;
end loop;
end if;
end loop;