0

Oracle 19c : Les partitions hybrides

twitterlinkedinmail

 

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 :

twitterlinkedinmail

Emmanuel RAMI

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.