Une vue matérialisée (VM) est un moyen simple de créer une vue physique d’une table. Comme son nom 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
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érent 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 SNAPSHOT CREATE MATERIALIZED VIEW et éventuellement
CREATE TABLE, CREATE VIEW,
CREATE INDEX CREATE ANY MATERIALIZED VIEW
REFRESH FAST SELECT ON la table de LOG concernée ou
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
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
- asynchrone, à la demande : clause ON DEMAND (utilise des procédures du package standard DBMS_MVIEW). C’est le mode par défaut
- 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)
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)
execute DBMS_REFRESH.REFRESH(’ref_group_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
