SelectEtoile : Base de connaissance SGBD

Rebuild d'indexes partitionnés parallélisés entre partitions et intra-partitions

1

Un package standard Oracle permet de paralléliser la reconstruction des partions d’index.

Le parallélisme est entre les partitions et au sein de chaque partition.

 

Exemple sur une table de 38 millions de lignes sur un index unique composite : 4 minutes !

 

SQL> connect / as sysdba

Connected.

SQL> alter system set job_queue_processes=16;

 

System altered.

 

SQL> connect user/pass

Connected.

 

SQL> create UNIQUE index USER.ITEM_COMP_MATCHID_ID on USER.ITEM(MATCHID,ID) LOCAL tablespace USER_ITEM_IDX storage (buffer_pool RECYCLE) unusable;

 

Index created.

 

Elapsed: 00:00:00.12

 

SQL> set serveroutput on size 10000

SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'ITEM','ITEM_COMP_MATCHID_ID',TRUE);                                                                  

INFO: Job #21 created for partition ITEM_001 with 4 slaves

INFO: Job #22 created for partition ITEM_002 with 4 slaves

INFO: Job #23 created for partition ITEM_003 with 4 slaves

INFO: Job #24 created for partition ITEM_004 with 4 slaves

INFO: Job #25 created for partition ITEM_005 with 4 slaves

INFO: Job #26 created for partition ITEM_006 with 4 slaves

INFO: Job #27 created for partition ITEM_007 with 4 slaves

INFO: Job #28 created for partition ITEM_008 with 4 slaves

INFO: Job #29 created for partition ITEM_009 with 4 slaves

INFO: Job #30 created for partition ITEM_010 with 4 slaves

INFO: Job #31 created for partition ITEM_011 with 4 slaves

INFO: Job #32 created for partition ITEM_012 with 4 slaves

INFO: Job #33 created for partition ITEM_UNMATCHED with 4 slaves

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:00.73

Ajouter un Commentaire


Code de sécurité
Rafraîchir