Hello
il ne m’a pas été encore possible de tester la nouvelle version Oracle 21c, dans la mesure ou cette version est pour le moment “Cloud only”. Je vous propose donc, pour le moment, un article au sujet d’une des nouveautés intéressantes du moteur Oracle 19c.
Il s’agit, pour l’option partitionning, des partitions dites “hybrides” sur les tables Oracle.
Nous savons que depuis la version 12c, Oracle prend en charge la partie partitionning sur des données provenant de source externes (fichiers csv, datapump….).
Avec la version 19c, il est possible de ‘mixer’ la source de données au sein d’une même table dans des partitions contenant à la fois, données de sources internes, et données de sources externes.
C’est notamment très pratique lorsque l’on veut “merger” des données à la fois provenant d’un csv, mais aussi d’un datapump avec des données internes insérées de facon conventionnelle (INSERT INTO classique).
L’avantage, c’est obtenir au sein d’une même table un ensemble complet de données indexable, sans passer par des jointures internes entre plusieurs tables.
Les données externes peuvent provenir de différentes sources de données, à savoir
- ORACLE_LOADER
- ORACLE_DATAPUMP
- ORACLE_HDFS
- ORACLE_HIVE
Mise en place
Pour utiliser cette fonctionnalité, il faudra disposer
- d’une version Oracle 19c Enterprise Edition
- une base avec ou sans multitenant
- un schéma Oracle disposant des droits de création de tables et de droits d’accès en lecture/écriture sur les directories.
Création des directories pour les données externes
SQL> create directory dir1 as '/data/oradata/dir1'; SQL> create directory dir2 as '/data/oradata/dir2'; SQL> create directory dir3 as '/data/oradata/dir3';
SQL> grant read,write on directory dir1 to manu; SQL> grant read,write on directory dir2 to manu; SQL> grant read,write on directory dir3 to manu;
Pour notre atelier exemple, nous allons créer 3 “directories” Oracle ainsi qu’une table d’employés d’une société de service informatique.
Les données présentes au sein de cette table seront, l’id de l’employé, son job, et sa date de naissance.
Nous placerons des fichiers CSV répartis dans ces 3 directories différents.
Un premier fichier csv nommé “avant80.csv”à placer dans le répertoire “dir1” auparavant créé.
Un second csv nommé “avant90.csv”dans “dir2”.
Enfin un 3e fichier “avant2000.csv” dans “dir3”.
Pour le stockage des données internes, nous utiliserons le tablespace par défaut du schéma Oracle, à savoir le tablespace USERS.
Table hybride avec données ORACLE_LOADER
Le DDL de création de table, pour notre exemple, sera le suivant :
SQL> connect manu Enter password: Connected.
SQL> CREATE TABLE EMPLOYES (id NUMBER , job varchar2(20), naissance DATE)
TABLESPACE users
EXTERNAL PARTITION ATTRIBUTES
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir3
ACCESS PARAMETERS
(FIELDS TERMINATED BY ','
(id , job, naissance DATE 'dd-MON-yyyy')
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (naissance)
(PARTITION av80 VALUES LESS THAN (TO_DATE('01-Jan-1980','dd-MON-yyyy'))
EXTERNAL DEFAULT DIRECTORY dir1 LOCATION ('avant80.csv'),
PARTITION av90 VALUES LESS THAN (TO_DATE('01-Jan-1990','dd-MON-yyyy'))
EXTERNAL DEFAULT DIRECTORY dir2 LOCATION ('avant90.csv'),
PARTITION av2000 VALUES LESS THAN (TO_DATE('01-Jan-2000','dd-MON-yyyy'))
EXTERNAL LOCATION('avant2000.csv'),
PARTITION POST2000 VALUES LESS THAN (TO_DATE('01-Jan-2021','dd-MON-yyyy'))
)
/
Nous avons ici une table nommée EMPLOYES partitionnée par range sur le champs “naissance”, avec un stockage par défaut des données internes sur le tablespace USERS, et un stockage des données externes par défaut dans le directory “dir3”.
Les données propres à la partition av90 sont dans le fichier CSV du directory “dir2” , la partition av80 contient les données du fichier CSV du directory “dir1”.
[oracle@ oradata]$ pwd /data/oradata [oracle@ oradata]$ ls -l d* dir1: total 4 -rw-r--r--. 1 oracle dba 220 Jun 4 08:28 avant80.csv dir2: total 4 -rw-r--r--. 1 oracle dba 241 Jun 4 08:29 avant90.csv dir3: total 4 -rw-r--r--. 1 oracle dba 281 Jun 4 08:29 avant2000.csv
Utilisation
Notre table est bien marquée comme table contenant des données hybrides dans le dictionnaire de données Oracle
SQL> col TABLE_NAME for a20 SQL> SELECT table_name, hybrid FROM user_tables; TABLE_NAME HYB -------------------- --- EMPLOYES YES
Celle ci ne contient, pour le moment, que des données externes, provenant de nos CSV, pas de données d’employés nés après 2000, car nous n’en avons pas inséré dans la partition correspondant à cette valeur.
SQL> select job , count(id) from employes group by job order by 2;
JOB COUNT(ID)
-------------------- ----------
ARCHITECTE SYS 1
ARCHITECTE SI 1
DSI 1
DAF 1
DIRECTEUR 1
DRH 2
ASSOCIE 2
ADMINISTRATEUR SYS 2
COMMERCIAL 3
DEVOPS 4
DBA 4
DEVELOPPEUR 6
12 rows selected.
SQL> select id, job, naissance from employes where naissance > to_date('01-JAN-00');
no rows selected
Nous alimentons la partition des données “Post 2000”, celle ci est une partition interne classique.
SQL> insert into employes values(210,'STAGIAIRE','10-MAR-2001');
1 row created.
SQL> insert into employes values(220,'DEVOPS JUNIOR','08-APR-2002');
1 row created.
SQL> commit;
Commit complete.
SQL> select id, job, naissance from employes where naissance > to_date('01-JAN-00');
ID JOB NAISSANCE
---------- -------------------- ---------
210 STAGIAIRE 10-MAR-01
220 DEVOPS JUNIOR 08-APR-02
Mais bien entendu, nous ne pouvons alimenter les partitions externes de cette table
SQL> insert into employes values(121,'DBA','12-MAR-1996'); insert into employes values(121,'DBA','12-MAR-1996') * ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
En effet, il faudra aller directement alimenter le fichier CSV sur le serveur :
[oracle@ dir3]$ echo "121,DBA,12-Mar-1996" >> avant2000.csv
Et vérifier que notre ligne existe bien dans la table, et surtout, dans la bonne partition “av2000” pour notre DBA portant l’ID 121 :
SQL> select id, job, naissance from employes partition (av2000) where ID=121; ID JOB NAISSANCE ---------- -------------------- --------- 121 DBA 12-MAR-96
/!\ Lorsque la donnée externe est insérée à la volée dans un fichier CSV, comme nous venons de le faire, c’est à l’utilisateur de bien faire attention à la partition à laquelle appartient ce fichier.
En effet, si nous avions inséré la ligne de notre nouveau DBA portant l’ID 121 de 1996 dans la partition “av80”, par exemple, Oracle n’aurait pas pu automatiquement changé la partition par défaut de celui ci. La ligne “121,DBA,12-Mar-1996” serait donc rester dans la partition “av80”, ce qui consisterait en une erreur logique.
Oracle ne sait organiser la donnée dans une partition que lorsqu’il s’agit d’une partition de données internes, via un INSERT INTO classique (bon à savoir).
C’était déjà le cas dans la version Oracle12c et la prise en charge des partitions externes.
Table hybride avec données ORACLE_DATAPUMP
Pour cette exemple, nous allons créer une table externe, avec un dumpfile, issue de notre table des employés mais ne contenant que les développeurs.
SQL> CREATE TABLE EMPLOYES_DEV
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION ('empl_dev.dmp')
) AS
SELECT ID, JOB, NAISSANCE from employes
WHERE JOB = 'DEVELOPPEUR'
/
Table created.
On s’assure que le dump est bien la, puis on supprime la table Oracle (bien entendu cela ne supprime pas le dumpfile)
[oracle@ dir1]$ ls -l
total 20
-rw-r--r--. 1 oracle dba 220 Jun 4 08:28 avant80.csv
-rw-r-----. 1 oracle dba 12288 Jun 4 09:45 empl_dev.dmp
-rw-r--r--. 1 oracle dba 41 Jun 4 09:45 EMPLOYES_DEV_1906.log
SQL> drop table EMPLOYES_DEV;
Table dropped.
Par la suite, créer une autre table partitionnée avec 1 partition interne pour les employés ayant le job DBA, et une partition externe dédiée pour les développeurs (oui je sais ce n’est pas très “corporate” pour nos amis développeurs ….).
SQL> CREATE TABLE EMPLOYES_JOB (id NUMBER , job varchar2(20), naissance DATE)
TABLESPACE users
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
)
PARTITION BY LIST (job)
(
PARTITION dba VALUES ('DBA'),
PARTITION developpeur VALUES ('DEVELOPPEUR') EXTERNAL
LOCATION ('empl_dev.dmp')
);
Table created.
la table est bien créée en mode hybride
SQL> SELECT table_name, hybrid FROM user_tables where hybrid = 'YES'; TABLE_NAME HYB -------------------- --- EMPLOYES YES EMPLOYES_JOB YES
Nous retrouvons les données externes des 6 développeurs de notre société dans cette table
SQL> select JOB,count(id) from employes_job partition(developpeur) group by job; JOB COUNT(ID) -------------------- ---------- DEVELOPPEUR 6
Auxquels nous pourrons rajouter des DBA sur la partition interne nommée dba.
SQL> insert into employes_job values(121,'DBA','12-MAR-1996'); 1 row created. SQL> insert into employes_job values(122,'DBA','30-JUL-1979'); 1 row created. SQL> select id,job,naissance from employes_job partition(dba) ; ID JOB NAISSANCE ---------- -------------------- --------- 121 DBA 12-MAR-96 122 DBA 30-JUL-79
La table EMPLOYES_JOB comporte donc également un mix des 2 partitions, externe et interne
SQL> select JOB,count(id) from employes_job group by job; JOB COUNT(ID) -------------------- ---------- DBA 2 DEVELOPPEUR 6
Ajout / suppression de partition
Il sera bien sur possible d’ajouter ou bien supprimer une partition dans une table à partitions hybrides.
S’il l’on reprend notre table EMPLOYES, voici les partitions que celle ci comporte
SQL> col HIGH_VALUE for a25
SQL> set long 25
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
from user_tab_partitions where TABLE_NAME='EMPLOYES';
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ------------------------- ----------------
EMPLOYES AV2000 TO_DATE(' 2000-01-01 00:0 SYSTEM
EMPLOYES AV80 TO_DATE(' 1980-01-01 00:0 SYSTEM
EMPLOYES AV90 TO_DATE(' 1990-01-01 00:0 SYSTEM
EMPLOYES POST2000 TO_DATE(' 2021-01-01 00:0 USERS
Supprimons la partition AV80 par exemple
SQL> alter table EMPLOYES drop partition AV80;
Table altered.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
from user_tab_partitions where TABLE_NAME='EMPLOYES';
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ------------------------- ----------------
EMPLOYES AV2000 TO_DATE(' 2000-01-01 00:0 SYSTEM
EMPLOYES AV90 TO_DATE(' 1990-01-01 00:0 SYSTEM
EMPLOYES POST2000 TO_DATE(' 2021-01-01 00:0 USERS
SQL> select job,naissance from EMPLOYES where naissance between to_date('01-JAN-70')
and to_date('01-JAN-80');
no rows selected
Ajoutons une autre partition externe, avec les enfants des employés par exemple ! Attention, dans cet exercice, il faudra “splitter” la dernière partition car celle ci comporte toutes données jusqu’à 2021.
SQL> alter table EMPLOYES split partition POST2000
at (to_date('01-Jan-2010','dd-MON-yyyy'));
Table altered.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
from user_tab_partitions where TABLE_NAME='EMPLOYES';
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ------------------------- ----------------
EMPLOYES AV2000 TO_DATE(' 2000-01-01 00:0 SYSTEM
EMPLOYES AV90 TO_DATE(' 1990-01-01 00:0 SYSTEM
EMPLOYES SYS_P241 TO_DATE(' 2010-01-01 00:0 USERS
EMPLOYES SYS_P242 TO_DATE(' 2021-01-01 00:0 USERS
Une fois splitté, la partition prend des noms définis par Oracle (SYS_). Il s’agira maintenant de supprimer la dernière partition ne contenant aucune donnée, puis renommer la partition des données inférieures à 2010.
SQL> select count(*) from EMPLOYES partition (SYS_P242); COUNT(*) ---------- 0 SQL> alter table EMPLOYES drop partition SYS_P242; Table altered. SQL> alter table EMPLOYES rename partition SYS_P241 to AV2010; Table altered.
Nous pourrons alors créer des données externes dans un csv “avant2020.csv” du directory “dir1”
[oracle@ dir1]$ vi avant2020.csv [oracle@ dir1]$ cat avant2020.csv 1001,DIRECTEUR,12-Jan-2010 1002,ASSOCIE,01-Fev-2014 1003,ASSOCIE,08-Mar-2013 1004,ASSOCIE,10-Jun-2012
Puis déclarer cette nouvelle partition
SQL> alter table EMPLOYES add partition AV2020 VALUES
LESS THAN (TO_DATE('01-Jan-2020','dd-MON-yyyy'))
EXTERNAL DEFAULT DIRECTORY dir1 LOCATION ('avant2020.csv');
Table altered.
La partition a bien été prise en compte dans la table
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions
where TABLE_NAME='EMPLOYES';
TABLE_NAME PARTITION_NAME HIGH_VALUE
-------------------- -------------------- -------------------------
EMPLOYES AV2000 TO_DATE(' 2000-01-01 00:0
EMPLOYES AV90 TO_DATE(' 1990-01-01 00:0
EMPLOYES AV2010 TO_DATE(' 2010-01-01 00:0
EMPLOYES AV2020 TO_DATE(' 2020-01-01 00:0
SQL> select id, naissance from employes where naissance > to_date('01-JAN-2010');
ID NAISSANCE
---------- ---------
1001 12-JAN-10
1002 01-FEB-14
1003 08-MAR-13
1004 10-JUN-12
Autre exemple avec notre table EMPLOYES_JOB dont le partitionning est fait par LIST et dont une des partitions porte sur un fichier dump.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME='EMPLOYES_JOB'; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ------------------------- ---------------- EMPLOYES_JOB DBA 'DBA' USERS EMPLOYES_JOB DEVELOPPEUR 'DEVELOPPEUR' SYSTEM
Supprimons la partition des développeurs qui est externe. Nous n’avons alors plus que des DBAs !
SQL> alter table EMPLOYES_JOB drop partition DEVELOPPEUR; Table altered. SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME='EMPLOYES_JOB'; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME -------------------- -------------------- ------------------------- ----------------- EMPLOYES_JOB DBA 'DBA' USERS SQL> select JOB,count(id) from employes_job group by job; JOB COUNT(ID) -------------------- ---------- DBA 2
Et puis…. non, rajoutons la finalement (on a bien besoin de développeurs en fin de compte !)
SQL> alter table EMPLOYES_JOB add partition DEVELOPPEUR values ('DEVELOPPEUR')
external location ('empl_dev.dmp');
Table altered.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
from user_tab_partitions where TABLE_NAME='EMPLOYES_JOB';
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ------------------------- -----------------
EMPLOYES_JOB DBA 'DBA' USERS
EMPLOYES_JOB DEVELOPPEUR 'DEVELOPPEUR' USERS
SQL> select JOB,count(id) from employes_job group by job;
JOB COUNT(ID)
-------------------- ----------
DBA 2
DEVELOPPEUR 6
Nos développeurs sont à nouveau présents dans cette table.
Quelques limites
comme toute nouveauté Oracle fraichement lancée sur un moteur récent, la prise en charge des partitions hybrides comporte certaines limites d’utilisation.
- pas de partition HASH pour le type de partitionnement, seuls le RANGE et le LIST sont pris en compte
- comme nous avons pu le voir, Oracle sait classer dans la bonne partition logique toute donnée insérée via ordre SQL dans une partition interne. Pour la partition externe, c’est à l’utilisateur de faire attention à insérer la ligne dans le bon CSV correspondant si besoin de modification.
- Le partitionnement est hybride dans la mesure ou l’on peut mixer données internes et données externes, mais il ne sera pas possible de mixer des données externes provenant d’un fichier dump ET d’un CSV. Lors de l’interrogation d’une table avec partitions externes de type ORACLE_DATAPUMP, par exemple, vous rencontreriez cette erreur
SQL> select JOB,count(id) from employes_job group by job; ERROR: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-31619: invalid dump file "/data/oradata/dir1/avant80.csv"
- Pour supprimer une partition ayant l’attribut EXTERNAL, supprimer auparavant la partition attachée par son nom.
SQL> alter table EMPLOYES_JOB drop partition DEVELOPPEUR; SQL> alter table EMPLOYES_JOB drop external partition attributes();
🙂
Emmanuel RAMI
Continuez votre lecture sur le blog :
- AWS Oracle RDS Read Replicas : un Active Dataguard en mode PaaS ? (Emmanuel RAMI) [AWSNon classéOracle]
- Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
- Insert et Update en une seule fois avec MERGE (Benjamin VESAN) [SQL Server]
- Oracle RDS : récupérer les tracefiles, comment faire ? (Emmanuel RAMI) [AWSOracle]
- La migration Oracle vers PostgreSQL avec ora2pg (Emmanuel RAMI) [OraclePostgreSQL]