Dans le cas d'un partitionnement "BY REFERENCE", on va partitionner la table ZX00 sur le critère de la période de paie (PERPAI) et les autres tables ZX** sur la base de la clef étrangère (utilisant le numéro de dossier NUDOSS).
Par exemple :
CREATE TABLE HR.ZX00
( NUDOSS NUMBER(38) NOT NULL ,
SOCDOS CHAR (0003) NOT NULL ,
PGPDOS NUMBER(38) NOT NULL ,
IDGPRG NUMBER(38) NOT NULL ,
SOCCLE CHAR (0003) NOT NULL ,
MATRIC CHAR (0012) NOT NULL ,
NUDOSP NUMBER (0003, 0) NOT NULL ,
IDSITU CHAR (0002) NOT NULL ,
PERPAI CHAR (0008) NOT NULL ,
…
FLGNTG CHAR (0001) NOT NULL )
PARTITION BY LIST(PERPAI)
(PARTITION MT201401 VALUES ('MT201401') TABLESPACE HRZX_MT201401);
( NUDOSS NUMBER(38) NOT NULL ,( NUDOSS NUMBER(38) NOT NULL ,
SOCDOS CHAR (0003) NOT NULL ,
PGPDOS NUMBER(38) NOT NULL ,
IDGPRG NUMBER(38) NOT NULL ,
SOCCLE CHAR (0003) NOT NULL ,
MATRIC CHAR (0012) NOT NULL ,
NUDOSP NUMBER (0003, 0) NOT NULL ,
IDSITU CHAR (0002) NOT NULL ,
PERPAI CHAR (0008) NOT NULL ,
…
FLGNTG CHAR (0001) NOT NULL )
PARTITION BY LIST(PERPAI)
(PARTITION MT201401 VALUES ('MT201401') TABLESPACE HRZX_MT201401);
ALTER TABLE
HR.ZX00 -- global
ADD CONSTRAINT PKZX00
PRIMARY KEY (NUDOSS)
USING INDEX TABLESPACE HRZXI
;
ALTER TABLE HR.ZX00
ADD
CONSTRAINT X2ZX00
UNIQUE
(
SOCCLE ,
PERPAI ,
...
TIMEST )
USING INDEX
LOCAL (PARTITION MT201401 TABLESPACE HRZXI_MT201401)
;
CREATE
INDEX HR.X4ZX00 ON
HR.ZX00
(NUGEST ,
NUDOSP )
LOCAL (PARTITION MT201401 TABLESPACE HRZXI_MT201401)
; CREATE TABLE HR.ZXTA NULIGN NUMBER(38) NOT NULL ,
SOCDOS CHAR (0003) NOT NULL ,
…
PERPAI CHAR (0008) NOT NULL ,
USAGEP CHAR (0001) NOT NULL ,
NUMTRT CHAR (0001) NOT NULL ,
NUMBUL CHAR (0002) NOT NULL ,
TIMEST DATE NOT NULL ,
CONSTRAINT FKZXTA
FOREIGN KEY (NUDOSS ) REFERENCES HR.ZX00
ON DELETE CASCADE )
PARTITION BY REFERENCE (FKZXTA)
;
ALTER TABLE HR.ZXTA
ADD CONSTRAINT X1ZXTA UNIQUE
(NUDOSS ,
NULIGN )
USING INDEX
LOCAL (PARTITION MT201401 TABLESPACE HRZXI_MT201401) ;
L'avantage avec un partitionnement « BY REFERENCE » c'est que l'absence de champ PERPAI dans la table ZX** n’est pas impactante, et que la création des partitions de toutes les tables filles (et des index) se fait automatiquement suivant celles de la ZX00.
Évitez d'indiquer des "tablespaces par défaut" : si vous avez indiqué un tablespace par défaut et que vous souhaitez pointer sur un nouveau tablespace, alors il est nécessaire de les modifier dans tous les objets avant chaque création de partition ...
Par exemple pour créer les tablespaces de données et d’index pour la nouvelle période de paie 201402 :
ALTER TABLE ZX00 MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZX_MT201402;
…
ALTER TABLE ZXTA MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZX_MT201402;
…
ALTER INDEX X2ZX00 MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZXI_MT201402;
ALTER INDEX X4ZX00 MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZXI_MT201402;
…
ALTER INDEX X1ZXTA MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZXI_MT201402;
ALTER INDEX X2ZXTA MODIFY DEFAULT ATTRIBUTES TABLESPACE HRZXI_MT201402;
…
Puis créer la partition sur ZX00 (et en cascade sur les autres tables et index),
ALTER TABLE HR.ZX00 ADD PARTITION MT201402 VALUES ('MT201402');
Cas de la position des partitions
Si l'on demande à Oracle le DDL d'une des tables partitionnées on constate que l'ordre des partitions n'est pas intuitif :SELECT dbms_metadata.get_ddl('TABLE','ZXTA') FROM DUAL;
...
PARTITION "MT201606" VALUES ('MT201606')
PARTITION "MT201608" VALUES ('MT201608')
PARTITION "MT201609" VALUES ('MT201609')
PARTITION "MT201610" VALUES ('MT201610')
PARTITION "MT201611" VALUES ('MT201611')
PARTITION "MT201308" VALUES ('MT201308')
PARTITION "MT201309" VALUES ('MT201309')
PARTITION "MT201310" VALUES ('MT201310')
PARTITION "MT201311" VALUES ('MT201311')
PARTITION "MT201312" VALUES ('MT201312')
PARTITION "MT201612" VALUES ('MT201612')
PARTITION "MT201607" VALUES ('MT201607')
Il est en fait lié à l'ordre de leur création. Et cet ordre peut être retrouvé dans le paramètre "partition_position".
Exemple (avec des sous-partitions) :
SQL> select TABLE_NAME , PARTITION_NAME , PARTITION_POSITION from all_tab_partitions where table_name ='ZX00' ;
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
ZX00 MT201401 1
ZX00 MT201402 2
...
ZX00 MT201611 34
ZX00 MT201308 35
ZX00 MT201309 36
ZX00 MT201310 37
ZX00 MT201311 38
ZX00 MT201312 39
ZX00 MT201612 40
ZX00 MT201607 41
SQL> select table_name , partition _name , subpartition_name , SUBPARTITION_POSITION from all_tab_subpartitions where table_name ='ZX00' ;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUBPARTITION_POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------
ZX00 MT201308 MT201308_SP4 1
ZX00 MT201308 MT201308_SP3 2
ZX00 MT201308 MT201308_SP1 3
ZX00 MT201308 MT201308_SP2 4
ZX00 MT201309 MT201309_SP4 1
ZX00 MT201309 MT201309_SP3 2
ZX00 MT201309 MT201309_SP1 3
ZX00 MT201309 MT201309_SP2 4
Michel a noté que ce mécanisme peut poser problème.
Sous Oracle 11g, pour savoir dans quelle partition / sous partition se trouve un enregistrement d'une table partitionnée (ici par exemple pour le dossier 58530 de la table ZX6P), exécuter les requêtes suivantes :
SQL> select dbms_rowid.rowid_object(ROWID) from ZXTA where nudoss = 58530 ;
DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
336890
SQL> select substr(OBJECT_NAME,1,15) , OBJECT_TYPE , SUBOBJECT_NAME from user_objects where data_object_id =336890 ;
SUBSTR(OBJECT_N OBJECT_TYPE SUBOBJECT_NAME
--------------- ------------------- ------------------------------
ZXTA TABLE PARTITION MT201601_SP1
En droppant et recréant la ZXTA sans respecter le PARTITION_POSITION, les insertions de données se font dans les mauvaises partitions...
SQL> insert into ZXTA select * from ZXTA_SAVE where nudoss in (select nudoss from ZX00 where perpai ='MT201402') ;
615 rows created.
SQL> select distinct dbms_rowid.rowid_object(b.ROWID) from ZX00 a , ZXTA b
where a.nudoss=b.nudoss and a.perpai = 'MT201402';
DBMS_ROWID.ROWID_OBJECT(B.ROWID)
--------------------------------
607680
SQL> select data_object_id , OBJECT_NAME , SUBOBJECT_NAME from user_objects where data_object_id = 607680 ;
DATA_OBJECT_ID OBJECT_NAME SUBOBJECT_NAME
-------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
607680 ZXTA MT201606
La documentation Oracle évoque cette contrainte (pour le LOB_partition_storage) mais rien n'est explicite dans le chapitre "table_partition_description" :
https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_7002.htm
Conclusion
Il faut comprendre que Oracle n'oblige pas à ce que l'on nomme les partitions de la table fille du même nom que la table mère (c'est implicite quand les partitions filles sont créées en cascade, mais pas dans le cas d'un CREATE isolé). C'est pourquoi cette "partition position" est primordiale.En conséquence, s'il est nécessaire de (re)créer une table partitionnée "BY REFERENCE"
- Pour les tables ne précisez pas les partitions, ni de tablespace par défaut : Oracle prendra modèle sur la ZX00 et créera les partitions dans le bon ordre et dans les mêmes tablespaces que ceux de la ZX00. Si vous devez préciser la liste des partitions (par exemple pour pointer sur des tablespaces différents), il est indispensable de reprendre l'ordre donné par le PARTITION POSITION de la ZX00.
- Pour les index partitionnés, ne précisez pas les partitions, ni de tablespace par défaut : Oracle prendra modèle sur la ZX00 (les index et les data se retrouvent dans le même tablespace que les données). Si vous devez préciser la liste des partitions (par exemple pour pointer sur des tablespaces différents), il est indispensable de reprendre l'ordre donné par le PARTITION POSITION de la ZX00
CREATE TABLE ZXTA
( ... ,
CONSTRAINT FKZXTA
FOREIGN KEY (NUDOSS) REFERENCES ZX00
ON DELETE CASCADE )
PARTITION BY REFERENCE (FKZXTA);
CREATE INDEX X6ZXTA ON ZXTA(...)
LOCAL (
PARTITION MT201401 ,
PARTITION MT201402 ,
PARTITION MT201403 ) ;
PS : si le nombre de partitions précisé est inférieur au nombre de partitions de la table mère vous aurez une erreur :
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
Aucun commentaire:
Enregistrer un commentaire