并行重建本地分区索引

DBMS_PCLXUTIL工具包使用方法：
DBMS_PCLXUTIL.BUILD_PART_INDEX (
   jobs_per_batch  IN NUMBER   DEFAULT 1,
   procs_per_job   IN NUMBER   DEFAULT 1,
   tab_name        IN VARCHAR2 DEFAULT NULL,
   idx_name        IN VARCHAR2 DEFAULT NULL,
   force_opt       IN BOOLEAN  DEFAULT FALSE); 

场景一：新建索引
 首先使用unusable选项建立一个失效状态的本地分区索引
CREATE INDEX <idx_name> on <tab_name>(...) local(...) unusable;
 其次是用build_part_index存储过程创建索引
EXECUTE dbms_pclxutil.build_part_index(4,4,<tab_name>,<idx_name>,FALSE);
force_opt指定为false时，将重建失效的索引分区。
此语句将执行4个分区索引的创建，并且每个分区索引将执行4个并行操作。

具体测试如下：

环境准备：
SQL> CREATE TABLE customers_part (
  2  customer_id        NUMBER(6),
  3  cust_first_name    VARCHAR2(20)
  4  )
  5  PARTITION BY RANGE (customer_id)
  6  (PARTITION p1 VALUES LESS THAN (1000),
  7  PARTITION p2 VALUES LESS THAN (2500),
  8  PARTITION p3 VALUES LESS THAN (MAXVALUE));
1  declare
  2  i number;
  3  begin
  4  for i in 1 .. 10000
  5  loop
  6  insert into customers_part values (i,'aa');
  7  commit;
  8  end loop;
  9* end;
SQL> /
SQL> CREATE INDEX par_ind on customers_part (cust_first_name) local unusable;

Index created.

SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'customers_part','par_ind',false);
BEGIN dbms_pclxutil.build_part_index(4,4,'customers_part','par_ind',false); END;

*
ERROR at line 1:
ORA-20001: Job queue processors are not initialised报错是由于没有修改job_queue_processes导致
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 287
ORA-06512: at line 1
SQL> alter system set job_queue_processes=30 scope=memory;
System altered.
SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'customers_part','par_ind',false);
PL/SQL procedure successfully completed

场景二：维护索引
首先使某个分区索引失效
ALTER INDEX <idx_name> modify (...) unusable;
重建失效索引
EXECUTE dbms_pclxutil.build_part_index(4,4,<tab_name>,<idx_name>,FALSE);
只重建实效的索引分区，如果指定为true将重建所有分区。
SQL> alter index par_ind modify partition p1 unusable;
Index altered.
SQL> select index_name,status from user_ind_partitions;
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
PAR_IND                                                      UNUSABLE
PAR_IND                                                      USABLE
PAR_IND                                                      USABLE
SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'customers_part','par_ind',false);
PL/SQL procedure successfully completed.

SQL> select index_name,status from user_ind_partitions;
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
PAR_IND                                                      USABLE
PAR_IND                                                      USABLE
PAR_IND                                                      USABLE