Récemment, j’ai eu l’occasion de travailler sur la compression des données lors d’une étude de faisabilité sur un projet de datawarehouse.
Ce sujet est intéressant à plusieurs titres :

• Les bases de données de grosse volumétrie demandent un investissement conséquent en terme de stockage et le coût est parfois un frein aux projets lorsque l’on atteint des volumétries de l’ordre de plusieurs dizaines de Tera octets.
• La compression n’entraine pas de pertes de performance (éventuellement un gain, à tester), car le temps perdu en ressources CPU lors de la décompression est largement compensé par des I/O beaucoup plus faibles.
• La compression de type basic est livrée sans coût supplémentaire avec la version Enterprise d’Oracle.

Il existe deux types de compression de table possibles dans les bases Oracle :

• la compression de type BASIC fournie par défaut, disponible depuis la version 9i, ne fonctionne qu’avec des opérations utilisant le mécanisme « direct path » et est limitée à des opérations d’insert. Les éventuelles modifications réalisées sur une ligne annulent la compression du block contenant cette ligne.
• la compression de type OLTP, disponible depuis la version 11g, qui est une option supplémentaire de la base Enterprise. Elle permet de réaliser des compressions à la volée sur chaque opération de mise à jour de la base de données.

Nous allons nous intéresser aujourd’hui à la compression de type BASIC. Ce type de compression s’adresse avant tout à des tables dont les données seront très peu ou pas du tout remises à jour. Les bases candidates sont donc les datawarehouses, les tables d’historiques, …
Principe de la compression de type BASIC
La compression des données Oracle s’effectue au niveau du block. On comprendra donc l’importance du tri des données lors de l’insertion en masse afin de maximiser le taux de compression. Ce type de compression, comme nous l’avons vu plus haut est possible à partir d’opérations d’insertion utilisant le mécanisme direct path, donc par exemple un hint « APPEND » dans une instruction insert de type « insert /*+ APPEND */ … into … select « , l’instruction CTAS « create table as select », chargement depuis sqlloader, mode bulk du PL/SQL, … La compression peut aussi être obtenue par une commande ALTER TABLE…MOVE COMPRESS, sachant qu’un lock exclusif est positionné sur la table durant l’opération (possibilité d’utiliser aussi le package dbms_redefinition).
Le taux de compression va varier suivant la répétitivité des données dans le block, il pourra être intéressant de trier les données lors de leur insertion dans la table.
Exemple d’application
Voici maintenant un exemple des résultats obtenus à partir d’une table de 25 Go qui va être recopiée dans une table partitionnée et compressée.
Les étapes de réalisation sont :

• Création de la table source
• Remplissage de la table source avec le script donné en annexe
• Création d’une table partitionnée compressée mais vide à partir de la table source
• Test du remplissage de la table partitionnée avec des données source triées
• Test du remplissage de la table partitionnée avec des données source non triées
• Résultats graphiques

1) Création de la table source

CREATE TABLE TAB_STOCK
  (
    "DATE_SNAP"          DATE NOT NULL ENABLE,
    "ID_SPOT"           NUMBER(6,0) NOT NULL ENABLE,
    "COD_SPOT"           NUMBER(10,0) NOT NULL ENABLE,
    "COD_TYPSPOT"        VARCHAR2(2 BYTE) NOT NULL ENABLE,
    "IDI_ART"            NUMBER(15,0) NOT NULL ENABLE,
    "COD_ART"            NUMBER(15,0) NOT NULL ENABLE,
    "COD_STATUT"         NUMBER(3,0) NOT NULL ENABLE,
    "PX_PAMP"            NUMBER(20,4) NOT NULL ENABLE,
    "PX_PV"              NUMBER(20,4) NOT NULL ENABLE,
    "MNT_STOCK_CPT"        NUMBER(20,4) NOT NULL ENABLE,
    "QTE_STOCK_CPT"        NUMBER(20,4) NOT NULL ENABLE,
    "MNT_STOCK_DISPO"      NUMBER(20,4) NOT NULL ENABLE,
    "QTE_STOCK_DISPO"      NUMBER(20,4) NOT NULL ENABLE,
    "MNT_STOCK_MRCH"       NUMBER(20,4) NOT NULL ENABLE,
    "QTE_STOCK_MRCH"       NUMBER(20,4) NOT NULL ENABLE,
    "MNT_STOCK_MRCH_REF"   NUMBER(20,4) NOT NULL ENABLE,
    "QTE_STOCK_MRCH_REF"   NUMBER(20,4) NOT NULL ENABLE,
    "MNT_STOCK_PHY"        NUMBER(20,4) NOT NULL ENABLE,
    "QTE_STOCK_PHY"        NUMBER(20,4) NOT NULL ENABLE,
    "DAT_INS" DATE NOT NULL ENABLE,
    "UPDATE_ID" VARCHAR2(26 BYTE) NOT NULL ENABLE,
    "DAT_MAJ" DATE NOT NULL ENABLE,
    "IDI_PI" NUMBER(6,0) NOT NULL ENABLE
  );

 
2) Remplissage de la table source
Vous pouvez utiliser le script livré en annexe pour effectuer des tests.
 
3) Création de la table destination partitionnée compressée
A noter la syntaxe COMPRESS synonyme de « COMPRESS BASIC ».

create table TAB_STOCK_PART_COMPRESS
PARTITION BY RANGE (DATE_SNAP)
( PARTITION P201212 VALUES LESS THAN (TO_DATE('01/12/2012', 'DD/MM/YYYY')))
TABLESPACE "USERS" PARALLEL COMPRESS
as select * from TAB_STOCK where 1=2;

 
4) Remplissage de la table partionnée compressée avec tri
L’insertion est réalisée en utilisant le parallélisme.

alter session enable parallel dml;
insert /*+ APPEND */
into TAB_STOCK_PART_COMPRESS
select  /*+ PARALLEL(16) */ * from TAB_STOCK
order by date_snap, id_spot, cod_spot, cod_typspot

 
5) Remplissage de la table partitionnée compressée sans tri
L’insertion est réalisée en utilisant le parallélisme.

alter session enable parallel dml;
insert /*+ APPEND */
into TAB_STOCK_PART_COMPRESS
select  /*+ PARALLEL(16) */ * from TAB_STOCK;

 
6) Résultats graphiques
Le taux de compression obtenu avec tri est de l’ordre de 5.
Sans tri, il est de l’ordre de 3,76.
Graphique de la volumétrie compressée obtenue avec tri

Graphique de la volumétrie compressée obtenue sans tri

 
Annexe
Voici un petit programme pl/sql à utiliser pour remplir une table quelconque avec des valeurs aléatoires. Il ne traite que les type NUMBER, VARCHAR2 et DATE. Je vous laisse le soin de le compléter éventuellement.

--------------------------------------------------------------------------
-- Le but de ce script est de composer des commandes insert
-- pour remplir une table avec des valeurs aléatoires
-- on compose chaque ligne à insérer avec des valeurs aleatoires typees
-- et bornées à partir des valeurs des colonnes dans le dictionnaire de donnees
--
-- Utilisation pour une table :
-- renseigner la variable nom_table avec le nom de la table a remplir
-- renseigner la variable nb_ligne_a_inserer avec le nombre de lignes a inserer
-- renseigner les bornes des colonnes de type DATE :
-- date_mini + plage_mini_date_aleatoire < plage aleatoire < date_mini + plage_max_date_aleatoire
--------------------------------------------------------------------------
declare
chaine varchar2(2000);
chaine_retour varchar2(2000);
commande_insert varchar2(2000);
nom_table varchar2(30) := 'TAB_STOCK';
nb_ligne_a_inserer number := 200;
date_mini varchar2(30) := '01/01/2012';
plage_mini_date_aleatoire varchar2(30) := '330';
plage_maxi_date_aleatoire varchar2(30) := '365';
TYPE cur_typ IS REF CURSOR;
c1 cur_typ;
begin
dbms_output.disable;
-- creation de la trame d'une commande insert à partir des types définis pour chaque colonne de la table TAB_STOCK
chaine := 'select
case when data_type=''NUMBER''  then to_char(trunc(DBMS_RANDOM.value(1,DATA_LENGTH))) ||'',''
     when data_type=''VARCHAR2'' then ''''''''||DBMS_RANDOM.STRING(''U'', CHAR_LENGTH) ||'''''',''
    when data_type=''DATE''     then
      ''to_date(''''''||to_date(TO_CHAR(TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(TO_DATE('''||date_mini||''',''dd/mm/yy''),''J''))+'||plage_mini_date_aleatoire||',
      TO_NUMBER(TO_CHAR(TO_DATE('''||date_mini||''',''dd/mm/yy''),''J''))+'||plage_maxi_date_aleatoire||')),''J'')))||'''''',''''DD/MM/YYYY HH24:MI''''),''
     end commande_insert
from dba_tab_columns
where table_name = '''||nom_table||
''' order by column_id';
--dbms_output.put_line(chaine);
for i in 1..nb_ligne_a_inserer loop    -- <======== A MODIFIER suivant le nombre de lignes que vous voulez insérer
OPEN c1 FOR chaine;
-- finition de la commande insert into table à partir de la commande chaine
commande_insert := 'insert into '||nom_table||' values (';
LOOP
        FETCH c1 into chaine_retour;
        EXIT WHEN c1%NOTFOUND;
        -- concatenation des lignes en retour pour composer la trame de la commande insert
        commande_insert := commande_insert ||chaine_retour;
    END LOOP;
    CLOSE c1;
-- astuce pour se debarasser de la derniere virgule et la remplacer par ')'
commande_insert := commande_insert || '#';
commande_insert := replace(commande_insert,',#',')');
-- la commande insert obtenu est du type :
-- insert into TAB_STOCK values (to_date('22/12/2012 00:00','DD/MM/YYYY HH24:MI'),21,18,9,'IA',12,21,18,12,3,10,17,11,16,5,11,20,3,21,4,to_date('28/12/2012 00:00','DD/MM/YYYY HH24:MI'),'FQWLEYEGZIQLGTEEFLTXSVJAVY',to_date('11/12/2012 00:00','DD/MM/YYYY HH24:MI'),8)
-- lancement des commandes d'insert obtenues
execute immediate(commande_insert);
end loop;
end;