Les vues materialisees

But des vues matérialisée s

· Stocker les résultats d’un ordre SQL dans une table

· Fournir à l’optimiseur un autre chemin d’accès (habituellement plus rapide) aux données

Principe

· Une MV peut être interprétée comme un index sur plusieurs tables

· Une MV peut être créée comme résultat d’ordres SQL complexes (jointures, agrégations)

Constitution

Techniquement, les vues matérialisées sont des SNAPSHOTS.

Privilèges

Celui qui crée des MV doit avoir les privilèges suivants :

· CREATE ANY VIEW

· CREATE TABLE

· CREATE ANY INDEX

· CREATE ANY MATERIALIZED VIEW ou bien

· CREATE ANY SNAPSHOT

· QUERY REWRITE

Déclenchement

L’option QUERY REWRITE est utile pour l’utilisation des MV lors de l’interprétation des ordres SQL semblables a celui qui a créée la vue. Pour déclencher son utilisation, la session courante doit pouvoir l’utiliser :

ALTER session SET QUERY_REWRITE_ENABLED = TRUE;

Exemple

L’exemple suivant prépare une vue matérialisée de deux tables jointes. La première table contient de noms de personnes et un Id. L’autre contient des Ids, les numéros de téléphone d’un Id (un ménage peut avoir plus de lignes ;-), et une quantité d’argent par numéro (dette). La vue contiendra la dette totale par ménage et par numéro. Dans cet exemple, ANP est le créateur de la vue, le host string est SLAV !

Preparation

set echo on

drop table anp.family;

drop table anp.calls;

drop materialized view family_debts_summary;

Création d’une des tables

create table anp.family (

id number (9),

name varchar2(30))

TABLESPACE TSANP;

Remplissage

insert into anp.family values (1,'Caulea');

insert into anp.family values (2,'Martin');

insert into anp.family values (3,'Colossus');

Création de l’autre table

create table anp.calls (

id number (9),

numero varchar2(30),

datecall date,

price number (5)

)

TABLESPACE TSANP;

Remplissage

insert into anp.calls values (1,'111111',sysdate,1);

insert into anp.calls values (1,'111111',sysdate,2);

insert into anp.calls values (1,'111112',sysdate,10);

insert into anp.calls values (1,'111112',sysdate,5);

insert into anp.calls values (2,'211111',sysdate,100);

insert into anp.calls values (2,'211111',sysdate,15);

insert into anp.calls values (2,'211112',sysdate,1);

insert into anp.calls values (3,'199991',sysdate,8);

insert into anp.calls values (3,'199991',sysdate,10);

insert into anp.calls values (3,'199992',sysdate,10);

insert into anp.calls values (3,'199992',sysdate,50);

insert into anp.calls values (3,'219999',sysdate,100);

insert into anp.calls values (3,'219999',sysdate,15);

insert into anp.calls values (3,'211112',sysdate,1);

insert into anp.calls values (3,'211112',sysdate,19);

Les privilèges du créateur de la vue

grant create any view to anp;

grant CREATE TABLE to anp;

grant CREATE any INDEX to anp;

grant CREATE any materialized VIEW to anp;

grant CREATE any snapshot to anp;

grant query rewrite to anp;

Création de la vue

connect anp/anp@slav

drop MATERIALIZED VIEW family_debts_summary;

CREATE MATERIALIZED VIEW family_debts_summary

TABLESPACE tsanp

PARALLEL (degree 4)

BUILD IMMEDIATE

REFRESH FAST

ENABLE QUERY REWRITE

SELECT fam.name,tel.numero,sum(tel.price)

FROM family fam, calls tel

WHERE (fam.id=tel.id)

GROUP BY fam.name, tel.numero

;

Interprétation des résultats des requêtes de vérification !

Preparation de l’analyse

analyze table anp.family compute statistics;

analyze table anp.calls compute statistics;

connect anp/anp@slav

Passons la requête, sans le SET_QUERY…

ALTER session SET QUERY_REWRITE_ENABLED = False;

SELECT fam.name,tel.numero,sum(tel.price)

FROM family fam, calls tel

WHERE (fam.id=tel.id)

GROUP BY fam.name, tel.numero

;

Oracle va de ce pas !

1.5 SELECT STATEMENT Cost = 5

2.1 SORT GROUP BY

3.1 HASH JOIN

4.1 TABLE ACCESS FULL FAMILY

4.2 TABLE ACCESS FULL CALLS

Passons la requête, avec le SET_QUERY…

ALTER session SET QUERY_REWRITE_ENABLED = TRUE;

SELECT fam.name,tel.numero,sum(tel.price)

FROM family fam, calls tel

WHERE (fam.id=tel.id)

GROUP BY fam.name, tel.numero

;

Oracle ira différemment chercher les données !

1.8 SELECT STATEMENT Cost = 8

2.1 TABLE ACCESS FULL FAMILY_DEBTS_SUMMARY

Conclusion

Bien que cet exemple soit à peu près débile, l’utilité des vues matérialisées est incontestable lors des traitements de tables gigantesques, en environnement DSS. J’attends les commentaires de ceux qui utilisent en production ce type de vues.

Radu Caulea, juin 1999