Une vue matérialisée (VM) est un moyen simple de créer une vue physique d’une table. Comme son m l’indique et ? la différence d’une vue standard, les données sont dupliquées. On l’ utilise ? des fins d’optimisation de performance, lorsque le select associé est particulièrement complexe ou lourd, ou pour faire des réplications de table.
La ‘fraicheur’ des données de la VM dépend des options choisies. Le décalage entre les données de la table maître et la VM peut être nul (raffraichissement synchrone) ou d’une durée planifiée : heure, jour,etc.
Suivant le contexte il existe différents types de VMs possibles : sur clé primaire, rowid, et plus ou moins complexes : avec fonctions aggrégées, sous requêtes, jointures, etc.

Bien que séduisants ces objets devront être utilisés avec parcimonie et ? bon escient. Il pourra être utile de faire des tests de refresh (complete et fast), de bien peser les volumétries, doser les fréquence de refresh en fonction des besoins réels, etc.

si on n’a pas l’option “réplication avancée” d’Oracle (Oracle Advanced replication option) les VM ne pourront être utilisées qu’en lecture seule

Syntaxe minimale

SQL > CREATE MATERIALIZED VIEW MV1
AS SELECT * FROM scott.emp

créé une vue matérialisée simple, copie conforme de EMP de SCOTT, avec par défaut un raffraîchissement ? la demande sur clé primaire.

par défaut le contenu de la VM est initialisé, la VM est remplie, lors de sa création. Il est possible de crééer une VM vide avec l’otion BUILD DEFERRED (le défaut est BUILD IMMEDIATE)

le CREATE créé aussi de manière transparente une table de même nom que la vue matérialisée et une clé primaire
Infos dans le référentiel / dictionnaire de données

Il existe essentiellement 2 tables sur les vues matérialisées : USER_MVIEWS et USER_MVIEW_LOGS dont voici les descriptions :

USER_MVIEWS
OWNER, MVIEW_NAME, CONTAINER_NAME, QUERY, QUERY_LEN , UPDATABLE, UPDATE_LOG, MASTER_ROLLBACK_SEG , MASTER_LINK, REWRITE_ENABLED , REWRITE_CAPABILITY , REFRESH_MODE, REFRESH_METHOD, BUILD_MODE , FAST_REFRESHABLE , LAST_REFRESH_TYPE, LAST_REFRESH_DATE, STALENESS , AFTER_FAST_REFRESH, UNKNOWN_PREBUILT, UNKNOWN_PLSQL_FUNC, UNKNOWN_EXTERNAL_TABLE, UNKNOWN_CONSIDER_FRESH, UNKNOWN_IMPORT, COMPILE_STATE, USE_NO_INDEX

USER_MVIEW_LOGS :
LOG_OWNER , MASTER , LOG_TABLE , LOG_TRIGGER, ROWIDS , PRIMARY_KEY , OBJECT_ID , FILTER_COLUMNS, SEQUENCE, INCLUDE_NEW_VALUES

Ainsi le create materialized view MV1 précédent nous renverrait (essentiellement) les infos suivantes dans le dictionnaire

select MVIEW_NAME, QUERY ,UPDATABLE, REFRESH_MODE, REFRESH_METHOD, BUILD_MODE, FAST_REFRESHABLE
from user_mviews

MVIEW_NAME
———-
QUERY
—–
UPDATABLE REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE
——— ———— ————– ———- —————-
MV1
SELECT “EMP”.”EMPNO” “EMPNO”,”EMP”.”ENAME” “ENAME”,”EMP”.”JOB” “JOB”,”EMP”.”MGR”…
N DEMAND COMPLETE IMMEDIATE DML

On vérifie également les objets créés implicitement :

SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
———— —————-
MV1 TABLE
MV1 MATERIALIZED VIEW
PK_EMPNO INDEX

le mot SNAPSHOT fréquemment utilisé est en fait en voie d’obsolescence et synonym ede ‘MVIEW’ dans le dictionnaire

Les droits et contraintes particulières

En général (sauf cas d’école) la vue matérialisée sera dans un schéma, voire dans le cas de réplication sur une base de données différente de la table maître. Dans ce cas la gestion des droits sera un peu plus délicate.
opération droit sur son propre schéma +  droit sur un autre schéma
CREATE MATERIALIZED VIEW + CREATE TABLE
REFRESH FAST SELECT ON la table de LOG concernée+  SELECT ON ANY TABLE ;-(
clause QUERY REWRITE QUERY REWRITE +  GLOBAL QUERY REWRITE
clause ON COMMIT REFRESH priv obj + ON COMMIT REFRESH priv system ON COMMIT REFRESH
clause PREBUILT TABLE priv SELECT + WITH GRANT OPTION sur table container

pour faire un faire un FAST REFRESH il faut un droit direct sur la table de LOG, un ROLE y compris DBA ou SYSDBA n’est pas suffisant. voir le fast refresh plus loin pour plus de détails

Pour une création simple il faut en résumé :

• le droit CREATE MATERIALIZED VIEW
• le droit CREATE TABLE (car une MV c’est une MV …. +  une TABLE)
• le GRANT SELECT sur les objets accédés par la vue

Note :

• on peut créer une vue matérialisée sur une vue
• on peut créer une vue matérialisée sur une vue matérialisée
• par défaut la table sous jacente doit avoir une clé primaire…

Les différents types de vues matérialisées

VM sur clé primaire
c’est le défaut. A utiliser sauf exception (voir paragraphe suivant)
VM sur rowid
SQL> CREATE MATERIALIZED VIEW MV2
REFRESH with ROWID
AS SELECT * FROM scott.emp

Utile lorsque la vue ne contient pas de (ou pas toutes les colonnes de la) clé primaire

contraintes (assez fortes !) :
* ne permet pas le fast refresh si il n’a pas eu un complet avant
* interdit si SELECT avec distinct, group by, connect by, fonctions d’aggregats, opérateur ensemblistes, sous requêtes

rem : on peut vérifier si le rafraichissement des VMs est actifs dans USER_JOBS

SQL> select what from user_jobs;

WHAT
——————————————————————————–
dbms_refresh.refresh(’”DDELEGLI”.”REF_GROUP_2?‘);
dbms_refresh.refresh(’”DDELEGLI”.”REF_GROUP_1?‘);

Modalités de rafraîchissement

Méthodes de refresh

il existe 3 méthodes : FAST, COMPLETE ou FORCE

• FAST REFRESH ( incrémental) c’est la méthode la + efficace, elle utilise des journaux spécifiques traçant les modification de la table maître : les LOGs.( Voir l’instruction CREATE MATERIALIZED VIEW LOG…)

• COMPLETE effectue le refresh complet en executant le SELECT de définition de la MV

• FORCE effectue un FAST si possible, sinon un COMPLET. C’est la méthode par défaut.

rappel : le fait que la VM soit initialisée avec des valeurs ou rafraichie ou non n’est pas du tout lié à la méthode (y compris FORCE!) cela dépend respectivement de l’option ‘build’ pour l’initialisation et du mode, synchrone ou non pour le raffraichissement…
méthode complete
c’est la méthode par défaut. Toutes les lignes de la table maître sont (re) synchronisées. Ca peut donc couter du temps mais ca fait les économies de place de la table log….

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée

méthode refresh partiel ou rapide ou ‘fast’

Si on essaie de faire la meme VM avec un refresh FAST…on obtient une erreur :

SQL> create materialized view mv2
2 refresh FAST
3 as select * from scott.emp;
as select * from scott.emp
*
ERREUR a la ligne 3 : ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log

C’est très clair (on l’a dit + haut) avec cette méthode ‘incrémentale’ il faut tracer les mises a jour faites sur la table maitre dans des fichiers LOG…encore faut il qu’ils existent !
On le fait (faute de mieux ou de droits adéquats ?) dans le compte maitre (le compte de SCOTT) et après on eut faire des FAST REFRESH…

SQL> connect scott/tiger
SQL> create materialized view log on emp;
Journal de vue materialisee cree.

Les logs, comme on peut le voir dans le référentiel sur les vues matérialisées, prennent le nom de la table maître préfixé par ‘MLOG$_’. Donc ici MLOG$_EMP.
on retente le coup et on obtient encore une erreur ORA-1031 “insufficient privilege” car on n’a pas les accèsm nécessaires, non pas sur SCOTT.EMP mais sur SCOTT.MLOG$EMP.
On pourra donner un SELECT ANY TABLE (c’est la solution parfois proposée dans le forums) ou plus finement un
SQL> GRANT SELECT ON SCOTT.MLOG$_EMP TO le_user_propriétaire_de_la_VM

Modes de refresh

Globalement synchrone (répercuté en temps réel) ou asynchrone (mis dans une queue et traité en différé) , mais il existe en fait 3 modes, 3 ‘fréquences’ de refresh :

• synchrone, sur commit : clause ON COMMIT
  par définition ne nécessite pas de commande REFRESH c’est la mise à jour COMMITée de la table maître qui déclenche automatiquement le REFERESH.

• asynchrone, à la demande : clause ON DEMAND. C’est le mode par défaut.
  On utilise la procédure standard DBMS_MVIEW.refresh qui est publique (DBMS_MVIEW est un synonyme public sur SYS.DBMS_SNAPSHOT et il existe un GRANT EXECUTE TO PUBLIC ON SYS.DBMS_SNAPSHOT ).
  Ne nécessite donc pas de droit particulier pour être exécuté (pour le rafraichissement d’une vue matérialisée dans un autre schéma voir les exemples ci-après).
  exemple :

SQL> execute DBMS_MVIEW.REFRESH(’MA_VM’);

• asynchrone, cyclique : clauses START WITH et NEXT, qui précise une date de début et une période de temps intermédiaire

on peut changer de fréquence de refresh avec la commande ALTER :

SQL> ALTER MATERIALIZED VIEW mv1
refresh complete
start with sysdate next sysdate + n

cette commande sans la clause NEXT, ne peut pas être dévoyée pour provoquer un refresh immediat d’une vue non à jour

Infos complémentaires sur la planification des refresh

L’automatisation du raffraichissement des vues matérialisées par Oracle se fait grace à des JOBS Oracle. On peut donc avoir des infos, notamment sur les refresh à venir, dans les vues du dictionnaires associées au JOBs.
En pratique il n’y a pas de colonne NEXT REFRESH dans les vues concernant les vues matérialisées, ce manque est donc comblé.

Ainsi la requête suivante nous donne des infos sur le LAST REFRESH, le NEXT REFRESH, et comment a été défini l’intervalle entre les 2 dans la vue matérialisée.

SQL> SELECT job, SCHEMA_USER,
TO_CHAR(last_date, ‘DD/MM/YYY HH:MI’) “Dernier refresh”,
TO_CHAR(next_date, ‘DD/MM/YYY HH:MI’) “Prochain refresh”,
interval “Intervalle”,
what “Refresh concerné”
FROM dba_jobs
WHERE what LIKE ‘%refresh%’
AND SCHEMA_USER=’SCOTT’

JOB SCHEMA_USER Dernier refresh Prochain refres Intervalle
Refresh concerné
229 SCOTT 11/09/008 05:00 12/09/008 05:00 trunc(sysdate + 1) + 17/24 dbms_refresh.refresh(’”SCOTT”.”MV1?‘);
230 SCOTT 12/09/008 08:00 14/09/008 12:00 trunc(sysdate + 1) + 1 dbms_refresh.refresh(’”SCOTT”.”MV2?‘);

Des exemples variés

• rafraichissement synchrone sur commit de la table maître (on commit refresh)

Des que les modifs de la table maitre sont commitées elle sont automatiquement synchronisées (répercutées) dans le VM.

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée
SQL> insert into scott.emp(empno, ename) values (1111,’DD’);
SQL> select ename from mv2 where empno=1111;
aucune ligne selectionnee
SQL> commit;
SQL> select ename from mv2 where empno=1111;
ENAME
—–
DD

• rafraichissement asynchrone à la demande (on demand)

SQL> execute DBMS_MVIEW.REFRESH(’MA_VM’);
SQL> — si la vue appartient à un autre schéma

SQL> connect system/xxx

SQL> grant ALTER ANY SNAPSHOT TO user1
SQL> connect user1/xxx
SQL> execute DBMS_MVIEW.REFRESH(’user2.MA_VM’);

• rafraichissement asynchrone à la demande (on demand) d’une vue d’un groupe

La vue matérialisée doit faire partie d’un groupe a raffraichir (refresh group)
On peut le creer avec le package DBMS_REFRESH et la procédure MAKE(). Une VM peut être ajouté lors de la
création du groupe ou plus tard avec la procédure ADD().
SQL> execute DBMS_REFRESH.MAKE(’ref_group_1',’MV1',null,null);
– cree le refresh group ‘ref_group_1' et y ajoute la VM ‘MV1'
Une fois cela fait on peut faire le refresh effectif.
On utilise encore le package DBMS_REFRESH et l’une de ses procédures de refresh : REFRESH(), REFRESH_ALL_MVIEWS, ou REFRESH_DEPENDENT)
SQL> execute DBMS_REFRESH.REFRESH(’ref_group_1');

Attention ! DBMS_REFRESH.refresh  comme son nom ne l’indique pas, ne sert qu’à rafraichir les GROUPES et nécessite des droits sur SYS.DBMS_REFRESH ! Pour une VM simple on utilise DBMS_MVIEW.refresh .
Dans le cas on on utilise DBMS_REFRESH.REFRESH pour une VM simple on obtient une erreur :

execute DBMS_REFRESH.REFRESH(’dd’);
BEGIN DBMS_REFRESH.REFRESH(’dd’); END;
*
ERROR at line 1:
ORA-23404: refresh group “HR”.”DD” does not exist
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REFRESH”, line 23
ORA-06512: at “SYS.DBMS_REFRESH”, line 195
ORA-06512: at line 1

• rafraichissement asynchrone cyclique (a date ou heure fixe)

on utilise la clause START WITH. SI elle est omise, Oracle se basera sur l’heure de création de la vue
matérialisée.
et la clause NEXT avec en paramètre une expression basée sur SYSDATE.
rafraîchissemebt auto tous les jours :

SQL> create materialized view mv4
refresh next sysdate + 1
as select * from scott.emp;
– et ci apres rafraîchissement toutes les minutes
– !!!!! pas vraiment conseillé… :
SQL> create materialized view mv4
refresh next sysdate + 1/24/60
as select * from scott.emp

• raffraichissement d’une vue matérilaisée  tous les jours à la même heure , tous les jours à 8H par exemple :

SQL> CREATE materialized view scott.mv1
refresh complete
START WITH trunc(sysdate) + 8/24
NEXT trunc(sysdate) + 32/24
WITH PRIMARY KEY
AS
SELECT * FROM scott.emp

• Exemple (pénible) de création de vue avec REFRESH ON COMMIT

SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.96
SQL> select * from scott.bonus;
DD

Elapsed: 00:00:01.05
SQL>
SQL> grant on commit refresh to system;

Grant succeeded.

Elapsed: 00:00:01.35
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-12014: table ‘BONUS’ does not contain a primary key constraint
Elapsed: 00:00:01.30
SQL> desc scott.bonus
Name Null? Type
—————————————– ——– —————————-
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER

SQL> alter table scott.bonus modify (ename primary key);

Table altered.

Elapsed: 00:00:00.30
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;

Materialized view created.

Vues utiles du dictionnaire concernant les VMs

SQL> select * from dict where table_name like ‘DBA_%MVIEW%’ ;

DBA_BASE_TABLE_MVIEWS : All materialized views with log(s) in the database
DBA_MVIEWS : All materialized views in the database
DBA_MVIEW_AGGREGATES : Description of the MV aggregates accessible to dba
DBA_MVIEW_ANALYSIS : Description of the materialized views accessible to dba
DBA_MVIEW_COMMENTS : Comments on all materialized views in the database
DBA_MVIEW_DETAIL_RELATIONS : Description of MV detail tables accessible to dba
DBA_MVIEW_JOINS : Description of join between 2 columns in the WHERE clause of a MV DBA_MVIEW_KEYS : Description of the columns that appear in the GROUP BY list of a materialized view accessible to dba
DBA_MVIEW_LOGS : All materialized view logs in the database
DBA_MVIEW_LOG_FILTER_COLS : All filter columns (excluding PK cols) being logged in the MV logs
DBA_MVIEW_REFRESH_TIMES : All fast refreshable materialized views and their last refresh times for each master table
DBA_REGISTERED_MVIEWS : Remote materialized views of local tables
DBA_REGISTERED_MVIEW_GROUPS : Materialized view repgroup registration information
DBA_TUNE_MVIEW : Catalog View to show the result after executing TUNE_MVIEW() API