dbms_pclxutil.build_part_index包可以用来以并行的方式rebuild local index。
包的来源文件是$ORACLE_HOME/rdbms/admin/dbmspclx.sql
该sql文件中可以看到包的用法：
procedure 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);
--
-- jobs_per_batch: #jobs to be created (1 <= job_count <= #partitions)
--
-- procs_per_job: #slaves per job (1 <= degree <= max_slaves)
--
-- tab_name: name of the partitioned table (an exception is
-- raised if the table does not exist or not
-- partitioned)
--
-- idx_name: name given to the local index (an exception is
-- raised if a local index is not created on the
-- table tab_name)
--
-- force_opt: if TRUE force rebuild of all partitioned indices;
-- otherwise rebuild only the partitions marked
-- 'UNUSABLE'
--
该包中只有这一个存储过程
SQL> desc dbms_pclxutil
PROCEDURE BUILD_PART_INDEX
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOBS_PER_BATCH NUMBER IN DEFAULT
PROCS_PER_JOB NUMBER IN DEFAULT
TAB_NAME VARCHAR2 IN DEFAULT
IDX_NAME VARCHAR2 IN DEFAULT
FORCE_OPT BOOLEAN IN DEFAULT
以如下语句为例，含义是每次job处理3个分区，每次处理的并行数为2。目标索引为DAY_PART的T_DAY_PART。TRUE代表local index全部rebuild，相应地FALSE代表只rebuild unusable的local index。
exec dbms_pclxutil.build_part_index(3, 2, 'DAY_PART', 'T_DAY_PART', TRUE);
通过10046可以看到递归sql
select toid from type$ where package_obj#=:1 and typ_name=:2
select oid$ from oid$ where obj#=:1
select name,password,datats#,tempts#,type#,defrole,resource$, ptime, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP
',defschclass), spare1,spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10 from user$ where user#=:1
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare
8, spare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where own
er#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
select obj#, prgoid, creation_time, mod_time, spec_time, flags from sys.scheduler$_lwjob_obj where userid = :1 an
d name = :2 and subname is null
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
insert into sys.scheduler$_job (program_oid, schedule_expr, schedule_id, queue_owner, queue_name, queue_agent, event
_rule, mxdur_msgid, last_enabled_time, class_oid, next_run_date, last_start_date, last_end_date, retry_count, run_co
unt, failure_count, running_instance, running_slave, flags, job_status, creator, client_id, guid, char_env, start_da
te, end_date, instance_id, fw_name, fw_oid, destination, credential_name, credential_owner, credential_oid, dest_oid
, job_dest_id, run_invoker, connect_credential_name, connect_credential_owner, connect_credential_oid, program_actio
n, schedule_limit, priority, job_weight, number_of_args, max_runs, max_failures, max_run_duration, comments, user_ca
llback, user_callback_ctx, nls_env, source, env, database_role, owner_udn, dist_flags, pdb_id, obj#) values (:1, :2,
:3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :2
7, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50
, :51, :52, :53, :54, :55, :56, :57, :58)
delete from dependency$ where d_obj#=:1
delete from access$ where d_obj#=:1
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,fla
gs,oid$,spare1,spare2,spare3, signature, spare7, spare8, spare9, dflcollid, creappid, creverid, modappid, modverid,
crepatchid, modpatchid) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18, :19,:20,:21,:22,decod
e(:23,0,null,:23),:24,:25,:26,:27,:28,:29)
select userid, name, subname, flags from sys.scheduler$_lwjob_obj where obj# = :1
update sys.scheduler$_job set last_enabled_time = :1, retry_count = :2, run_count = :3, failure_count = :4, job_sta
tus = :5 where obj# = :6
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13,sp
are2=:14,spare3=:15,signature=:16,spare7=:17,spare8=:18,spare9=:19, dflcollid=decode(:20,0,null,:20),creappid=:21,cr
everid=:22, modappid=:23,modverid=:24,crepatchid=:25,modpatchid=:26 where owner#=:1 and name=:2 and namespace=:3 and
remoteowner is null and linkname is null and subname is null
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
可以看到上述过程没有加锁，所以猜测该dbms包在rebuild local index的过程中，无法处理open transaction。
实验验证
MING@ming(MING)> SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN ('ID_DAY_PART','T_DAY_PART');
PARTITION_NAME INDEX_NAME STATUS
------------------------------ ------------------------- --------
P1 ID_DAY_PART USABLE
SYS_P420 ID_DAY_PART UNUSABLE
SYS_P421 ID_DAY_PART UNUSABLE
SYS_P422 ID_DAY_PART UNUSABLE
MING@ming(MING)> select * from DAY_PART partition(SYS_P422);
ID EITIME
---------- ------------
2 15-JAN-19
MING@ming(MING)> select * from DAY_PART partition(SYS_P421);
ID EITIME
---------- ------------
2 10-JAN-19
MING@ming(MING)> select * from DAY_PART partition(SYS_P420);
ID EITIME
---------- ------------
1 05-JAN-19
2 05-JAN-19
MING@ming(MING)> select * from DAY_PART partition(p1);
no rows selected
新开会话，不提交
MING@ming(MING)> update day_part set id=10000 where id=1;
1 row updated.
MING@ming(MING)> exec dbms_pclxutil.build_part_index(3, 2, 'DAY_PART', 'ID_DAY_PART', TRUE);
PL/SQL procedure successfully completed.
MING@ming(MING)> SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN ('ID_DAY_PART','T_DAY_PART');
PARTITION_NAME INDEX_NAME STATUS
------------------------------ ------------------------- --------
P1 ID_DAY_PART USABLE
SYS_P420 ID_DAY_PART UNUSABLE
SYS_P421 ID_DAY_PART USABLE
SYS_P422 ID_DAY_PART USABLE
SYS_P420分区索引还是unusable。
SQL> select * from dba_scheduler_running_jobs;
no rows selected
后台也没有正在运行的job，所以open transaction不会处理了。
新建一张分区表
INDEX_NAME STATUS
------------------------------ --------
ID_DAY_PART UNUSABLE
ID_DAY_PART UNUSABLE
ID_DAY_PART UNUSABLE
SQL> SELECT COUNT(*) FROM DAY_PART PARTITION(SYS_P3953);
COUNT(*)
----------
2000000
SQL> SELECT COUNT(*) FROM DAY_PART PARTITION(SYS_P4053);
COUNT(*)
----------
2000000
利用包重建索引
SQL> exec dbms_pclxutil.build_part_index(3, 2, 'DAY_PART', 'ID_DAY_PART', TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> SELECT INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='ID_DAY_PART';
INDEX_NAME STATUS
------------------------------ --------
ID_DAY_PART USABLE
ID_DAY_PART UNUSABLE
ID_DAY_PART UNUSABLE
Elapsed: 00:00:00.00
SQL> SELECT INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='ID_DAY_PART';
INDEX_NAME STATUS
------------------------------ --------
ID_DAY_PART USABLE
ID_DAY_PART UNUSABLE
ID_DAY_PART UNUSABLE
Elapsed: 00:00:00.00
SQL> SELECT INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='ID_DAY_PART';
INDEX_NAME STATUS
------------------------------ --------
ID_DAY_PART USABLE
ID_DAY_PART UNUSABLE
ID_DAY_PART USABLE
Elapsed: 00:00:00.00
SQL> SELECT INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='ID_DAY_PART';
INDEX_NAME STATUS
------------------------------ --------
ID_DAY_PART USABLE
ID_DAY_PART USABLE
ID_DAY_PART USABLE
Elapsed: 00:00:00.00
虽然包马上返回了结果，但是索引并没有马上重建完成，说明包的作用只是建立一个重建索引的job，job创建成功就返回，不管索引是否重建成功。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31480688/viewspace-2383043/ ，如需转载，请注明出处，否则将追究法律责任。
