把索引分区设置为不可用后重建
在数据仓库环境中装载大量数据时，由于索引的存在，批量dml操作的速度可能会非常慢。分区索引的关键好处之一是，可以在批量加载数据前，把索引的某些部分设置为unusable，然后在加载完成后，仅仅需要对那些受影响的部分索引分区进行重建。
从根本上说，先把索引标记为不可用，然后重建索引是相当简单的。例如:
SQL> alter table employees_part modify partition pmax unusable local indexes; Table altered. SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO' 2 union 3 select index_name,partition_name,status from user_ind_partitions where index_name in 4 (select index_name from user_indexes where table_name='EMPLOYEES_PART') 5 order by 1,2,3; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ---------------------------------------- -------- EMPLOYEES_PART_PK VALID EMPLOYEES_PART_UK1 MANAGER_100 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_200 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_300 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_400 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_500 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_600 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_700 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_800 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_900 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_MAX USABLE EMPLOYEE_PART_LI P1990 USABLE EMPLOYEE_PART_LI P1991 USABLE EMPLOYEE_PART_LI P1992 USABLE EMPLOYEE_PART_LI P1993 USABLE EMPLOYEE_PART_LI P1994 USABLE EMPLOYEE_PART_LI P1995 USABLE EMPLOYEE_PART_LI P1996 USABLE EMPLOYEE_PART_LI P1997 USABLE EMPLOYEE_PART_LI P1998 USABLE EMPLOYEE_PART_LI P1999 USABLE EMPLOYEE_PART_LI P2000 USABLE EMPLOYEE_PART_LI PMAX UNUSABLE 23 rows selected.
然后，在批量加载操作完成之后，就可以执行以下命令，重建那些受批量加载操作影响的索引分区:
SQL> alter table employees_part modify partition pmax rebuild unusable local indexes; Table altered. SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO' 2 union 3 select index_name,partition_name,status from user_ind_partitions where index_name in 4 (select index_name from user_indexes where table_name='EMPLOYEES_PART') 5 order by 1,2,3; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ---------------------------------------- -------- EMPLOYEES_PART_PK VALID EMPLOYEES_PART_UK1 MANAGER_100 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_200 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_300 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_400 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_500 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_600 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_700 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_800 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_900 UNUSABLE EMPLOYEES_PART_UK1 MANAGER_MAX USABLE EMPLOYEE_PART_LI P1990 USABLE EMPLOYEE_PART_LI P1991 USABLE EMPLOYEE_PART_LI P1992 USABLE EMPLOYEE_PART_LI P1993 USABLE EMPLOYEE_PART_LI P1994 USABLE EMPLOYEE_PART_LI P1995 USABLE EMPLOYEE_PART_LI P1996 USABLE EMPLOYEE_PART_LI P1997 USABLE EMPLOYEE_PART_LI P1998 USABLE EMPLOYEE_PART_LI P1999 USABLE EMPLOYEE_PART_LI P2000 USABLE EMPLOYEE_PART_LI PMAX USABLE 23 rows selected.
使用此命令重建索引有一个明显的缺点：索引分区是顺序建立的，即一次重建一个索引分区。如果表上有许多索引，那么用这个命令会减缓重建过程，如果此操作是常规性处理任务的一部分，那么这种减缓会令人难以接受。一种缓解alter table … rebuild local indexes unusable命令的串行限制的方法是，并行重建每个受影响的分区。
实现并行重建索引操作有好几种方法。