dbms_pclxutil.build_part_index包的用法

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创建成功就返回，不管索引是否重建成功。