Vues matérialisées

Comme une vue au sens classique d’Oracle, une vue matérialisée (”MaterializedView” ou “MV”) est une vue basée sur une requête SQL. A l’instar d’une vue classique, lorsque l’on crée une vue matérialisée, Oracle crée alors une table dont la structure correspond exactement à celle induite par le résultat du select associé. Les données de la vue matérialisée sont alors issues de la requête SQL et recopiées physiquement dans cette table, on dit alors que les données sont “matérialisées”.

1 - Introduction / Rappels :

Les avantages d’une vue matérialisée sont globalement dans la rapidité d’accès aux données, en effet, si les tables dont sont issues les requêtes SQL induisant les vues matérialisées sont complexes, le fait d’y accéder par l’intermédiaire d’une vue matérialisée en rendra l’accès extrêmement plus rapide puisque celle-ci sont en fait recopiées physiquement dans la table de la dite vue et de nécessite plus l’interprétation d’un plan d’exécution complexe pour les récupérer.

Les vues matérialisées sont globalement utilisées pour accélérer l’accès à des données dont le quotient de mise à jour demeure faible. En effet, si les données d’une MV sont matérialisées dans une table, il peut exister à un instant <T> un décalage entre les données réellement présentes dans les tables pointées par la requête SQL et les données physiquement recopiées, une mise à jour de ces données physique est alors nécessaire pour se rapprocher des données réelles.

Dans ce contexte, nous verrons qu’il existe plusieurs méthodes pour mettre à jour une vue matérialisées, plus ou moins adaptées à l’utilisation que les utilisateurs en font et permettant aussi de faire au sens d’Oracle de la réplication de données entre hôtes distants.

2 - Création/Suppression d’une Vue Matérialisée :

La création d’une vue matérialisée se fait par l’instruction :

create materialized view <nom_vue_materialisée> as <requête_SQL>
                    [ refresh <mode> [ <type> ] [ with <cycle> ]
                      [ FORCE | FAST ] ]

Exemple:

SQL> create materialized view mv_personnes

2 as select c.nom, c.prenom, v.nomville from clients c,
ville v where c.id_ville=v.id_ville;

SQL> select * from mv_personnes;

NOM      PRENOM      NOMVILLE

------   -------     -------------------------------
NOM_1    PRENOM_1    ANGOULEME
NOM_2    PRENOM_2    BASTIA

Une vue matérialisée se détruit par un drop au sens classique du terme :

SQL> drop materialized view mv_personnes;

Note: Les vues systèmes USER_MVIEWS et DBA_MVIEWS permettent aux utilisateurs et aux DBAs d’obtenir toutes les informations sur les vues matérialisées auxquels ils ont accès. Le champ “query” permet notament d’indentifier la requête SQL induisant la vue matérialisée et “mview_name” le nom de la table physique créée pour matérialiser les données de cette requête.

3 - Modes de rafraichissement des vues matérialisées :

Comme nous l’avons évoqué précédement, la pertinence d’une vue matérialisée est caractérisée par la fréquence de mise à jour (ou de synchronisation) de ses données par rapport aux données des tables mères sur laquelle porte la requête SQL qui l’induit. En clair : quelle est la méthode et la fréquence de rafraichissement des données matérialisées de la vue par rapport aux données réélles existant dans les tables pointées par la requête SQL de la vue matérialisée?

3.1) Rafraichissement synchrone sur validation

Le mode de rafraichissement sur validation des vues matérialisation est celui qui vous permet d’assurer une équivalence parfaite entre les données matérialisées et celles dynamiques issues de la requête SQL caractéristique. Chaque validation transactionelle (commit) aura pour effet de rafraichir les données de la MV, une donnée validée dans une table mère impliquera au préalable une donnée inscrite dans la table de la MV.

L’équivalence des données de la MV est donc assurée de manière synchrone par rapport aux données situées dans les tables pointées par la requête SQL caractérisant la vue.

C’est la plus éfficace des mises à jour en terme fonctionel, c’est la moins efficace en CPU et consomation mémoire. Une telle façon de fonctionner est éfficace si la MV se fait sur la même database contenant les tables mères pointées par la requête SQL. Si la MV est distante, des problèmes de performance apparaissent très vite et cette méthode est déconseillée.

En clair, cette méthode vous permet d’améliorer des requêtes lourdes sur une instance de base de données.

Syntaxe :

 create ... refresh on commit;

3.2) Rafraichissement asynchrone sur demande

Une MV en mode de rafraichissement sur demande, comme son nom l’indique, possède des données matérialisées qui sont mises à jour de façon evènementielle sur demande (administrative, par automatisme PLSQL, …). La méthode de rafraichissement est donnée par le package DBMS_REFRESH et la procédure REFRESH(’<nom_MV>’),

Syntaxe :

 create materialized view MV1 ...;
 ...
 execute DBMS_REFRESH.REFRESH('MV1');

Note: Dans le cadre d’une réplication de tables, le package DBMS_REFRESH possède la méthode REFRESH_ALL_MVIEWS qui permet de rafraichir toutes les MV sans les nommer une à une.

3.3) Rafraichissement asynchrone différentiel cyclique

Ce mode de rafraichissement est bien plus souvent utilisé que ces prédécesseurs car il économise les ressources de la base de donénes maître en réduisant le nombre de mises à jour nécessaires entre les MVs et les tables maitresses.

Le commutateur “start with” va permettre de définir une fréquence de mise à jour des données en collaboration avec le commutateur “next” qui permettra de définir l’intervalle de future mise à jour après la première éffectuée.

Syntaxe:

create materialized view MV1
refresh
start with trunc(sysdate+1)+ 8/24
next trunc(sysdate + 1) + 24/24
as select * from t1, t2;

Dans cet exemple, MV1 verra ses données matérialisées créées à 8 heures puis rafraichies automatiquement tous les jours à minuit.

4 - Type de rafraichissement FORCE ou FAST :

4.1 - Introduction :

Il existe deux “types” de rafraichissement des données matérialisées, le type FORCE ou le type FAST.

Le mode FORCE est le mode par défaut et implique le comportement de tous les exemples décrits dans les paragraphes précédents, il est donc inutile d’en reparler ici.

Le mode FAST implique une notion de mise à jour RAPIDE en opposition à une mise à jour plus lente qu’est la mise à jour complète des données d’une MV. Vous l’aurez compris (ou intuité), nous parlons ici d’une mise à jour différentielle des données matérialisées.

4.2 - Type de mise à jour Rapide des données matérialisées :

Le typde mise à jour “rapide” d’une MV est un type incrémental. Afin de minimiser le temps de comparaison des données présentes dans les tables mères par rapport aux données présentes dans les tables des MV, un fichier de journalisation des données modifiées est créé à cet effet, ou plustot une “table” de journalisation des données matérialisées : on parle de MATERIALIZED VIEW LOG (tables “MLOG$_*”).

Ces tables de log “MLOG$_*” comprennent les modifications éffectuées sur les tables mères d’une MV (insert, update, delete) et permettent au MV de ne synchroniser que les enregistrements ayant subits des modifications, au contraire du type FORCE qui concerne l’ensemble des données de la MV (et donc des tables mères).

Ce type de fonctionnalité est donc précédé de la création de ces tables de LOG grâce à la commande :

SQL> create materialized view log on T;

Ceci ayant pour effet de créer une table MLOG$_T sur le host courant, une table qui contiendra à dater de cet nstant toutes les modifications des lignes de la table T. Il conviendra par la suite de créer une vue matérialisée sur T pour bénéficier de ce journal.

La problématique de la fréquence des mises à jour ne chande pas (sur demande, on commit, …) sauf que celle ci se fera à dater de ce moment de manière différentielle et non plus de manière complète, ce qui représentera un gain de temps fondamental.

Il convient ensuite de préciser que l’on souhaite une vue matérialisée dont le typde mise à jour est basé sur l’exploitation de ces tables de log Oracle pour éffectuer des mises à jours incrémentales selon une fréquence à définir:

SQL> create materialized view MV REFRESH FAST on select * from T;

La fréquence de lecture des journaux (tables) de log seront induites par les sous commutateurs start et next permettant de définir une condition initiale pour débuter une mise à jour et une récurence.

5 - Réplication de données :

5.1 - Introduction :

La réplication de données selon Oracle est assurée par la mise à jour de données de tables par les méthodes de synchronisations de données éffectuées grâce au concept de vue matérialisée.

Une réplication de données se fera donc à l’aide de l’utilisation des vues matérialisées, des notions qui seront accessibles à distance à l’aide de la notion de DB-LINK (Database Link) / liaison inter-bases de données.

Le principe d’un DB-LINK est d’accéder de manière transparente à des tables situées en dehors de l’instance de base de données principale, généralement située sur un autre hote accessible en TCP/IP. Un DB-LINK Oracle permet donc de lier des schémas de base de données inter instances entre elles.

La création d’un DB-LINK se réalise par la syntaxe suivante :

CREATE PUBLIC DATABASE LINK CONNECT TO <user> IDENTIFIED BY <pwd> USING '<tsn_conn>' ;

Avec les paramètres suivants :

- <user> : nom d’utilistateur de la DB distante

- <pwd>: le password de <user>

- <tsn_conn> :  la définition d’accès à la database distance par le TNS_NAME local.

Imaginons maintenant, à travers la définition de DB-LINK, qu’une table distante soit accessible depuis une instance de base de données locale, si nous créons sur la table distante un journal de log, rien ne nous empêche sur la base locale de créer alors une vue matérialisée en mode FAST, vue induuite par une requête utilisant cette même table distante caractérisée par un journal de log. Nous venons à cet instant précis d’alimenter une table d’une instance I1 par une table d’une instance I2, le tout, quel que soit la distantce, sur un réseau TCP/IP : Autrement dit : nous venons de faire de la réplication de données.

5.2 - Mise en place :

Un petit peu pressé par le temps, je publie cet article maintenant en l’absence de rédaction de ce paragraphe. Sous peu, vous aurez sous le 5.2) l’exacte syntaxe pour mettre en place concrètement et rapidement la réplication de données selon Oracle (i.e. à travers des MV en type FAST).

En attendant, certaines informations vous seront peut être utiles, d’où la publication rapide !