« avancés » va peut-être vous faire rire, d’où les guillemets ! Quoiqu’il en soit, voici 2 exemples qui illustrent plusieurs possibilités des vues matérialisées et notamment :
- La possibilité de rafraîchir de manière rapide (FAST REFRESH), en appliquant uniquement les modifications, une vue matérialisée qui contient une jointure et/ou une clause GROUP BY
- La possibilité d’imbriquer (ou plutôt mettre en cascade) des vues matérialisées tout en conservant une mise à jour rapide
- L’utilisation de utlxmv.sql et DBMS_MVIEW.EXPLAIN_MVIEW pour comprendre pourquoi une vue matérialisée ne peut pas être rafraîchie avec la méthode « REFRESH FAST »
- L’utilisation de GROUPING SET dans une vue matérialisée pour avoir plusieurs niveaux d’agrégats dans la même vue matérialisée
Voilà pour ce post. Notez bien que j’ai testé ce qui suit sur 2 bases de données : Oracle 11.1.0.6 Enterprise Edition sur Linux et Oracle 10.2.0.2 Standard Edition. Moi qui peste toujours contre les limitations de la version SE, ce pourrait être l’exception qui confirme la règles ? Bien sur, je ne parle pas de « Query Rewrite 😉 »
Schéma d’exemple
La première difficulté de ce genre d’exercices c’est d’avoir des tables d’exemple représentatives. Construire quelque chose qui ressemble à un vrai problème prend du temps. Les schémas d’exemple d’Oracle, et en particulier SH (Sales History), dans le CD du même nom sont donc, le plus souvent, des candidats parfaits. Enfin, comme j’aime bien partir de 0 et que seul les aspects techniques sont vraiment intéressants, je vous propose de commencer par les scripts ci-dessous, pour créer quelques tables utiles pour les exemples qui suivent. De cette manière vous adapterez la taille de votre base de données selon vos besoins :
create table d1 (
id number,
lib varchar2(15),
attr1 number,
level1 number,
level2 number);
create table d2 (
id varchar2(10),
lib varchar2(10),
level1 varchar2(10));
create table d3 (
id date,
lib varchar2(80),
level1 date);
create table ftable (
d1 number,
d2 varchar2(10),
d3 date,
val1 number,
val2 number);
declare
v_d1 number := &d1_num_distinct_keys;
v_d2 number := &d2_num_distinct_keys;
v_d3 number := &d3_num_distinct_keys;
v_rows number := &fact_num_rows;
s_date date := trunc(sysdate,'DD');
begin
for i in 0.. v_d1-1 loop
insert into d1(id, lib, attr1, level1, level2)
values(i,
'Num #'||to_char(i),
mod(i,17),
mod(i,140),
mod(i,20));
end loop;
commit;
for j in 0..v_d2-1 loop
insert into d2(id, lib, level1)
values('#'||to_char(j),
'Char #'||to_char(j),
'$'||to_char(mod(j,31)));
end loop;
commit;
for k in 0..v_d3-1 loop
insert into d3(id, lib, level1)
values(s_date-k,
to_char(s_date-k,'Month Day, Year'),
trunc(s_date-k,'MM'));
end loop;
commit;
for l in 1..v_rows loop
insert into ftable(d1, d2, d3, val1, val2)
values (mod(l,v_d1),
'#'||mod(l,v_d2),
s_date - mod(l,v_d3),
mod(l,113),
trunc(100000/(mod(l,19)+1)));
end loop;
commit;
end;
/
Enter value for d1_num_distinct_keys: 120
old 2: v_d1 number := &d1_num_distinct_keys;
new 2: v_d1 number := 120;
Enter value for d2_num_distinct_keys: 23
old 3: v_d2 number := &d2_num_distinct_keys;
new 3: v_d2 number := 23;
Enter value for d3_num_distinct_keys: 365
old 4: v_d3 number := &d3_num_distinct_keys;
new 4: v_d3 number := 365;
Enter value for fact_num_rows: 150000
old 5: v_rows number := &fact_num_rows;
new 5: v_rows number := 150000;
alter table d1
add constraint d1_pk
primary key(id);
alter table d2
add constraint d2_pk
primary key(id);
alter table d3
add constraint d3_pk
primary key(id);
exec dbms_stats.gather_table_stats(user, 'D1');
exec dbms_stats.gather_table_stats(user, 'D2');
exec dbms_stats.gather_table_stats(user, 'D3');
exec dbms_stats.gather_table_stats(user, 'FTABLE');
set autotrace traceonly;
select d1.level2,
d2.level1,
d3.level1,
trunc(sum(ftable.val1)/1000)
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;
Execution Plan
-------------------------------------------------------
Plan hash value: 99612550
-------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 2990 | 742 |
| 1 | HASH GROUP BY | | 2990 | 742 |
|* 2 | HASH JOIN | | 125K| 151 |
| 3 | TABLE ACCESS FULL | D3 | 365 | 3 |
|* 4 | HASH JOIN | | 125K| 147 |
| 5 | TABLE ACCESS FULL | D1 | 120 | 3 |
|* 6 | HASH JOIN | | 125K| 142 |
| 7 | TABLE ACCESS FULL| D2 | 23 | 3 |
| 8 | TABLE ACCESS FULL| FTABLE | 125K| 138 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FTABLE"."D3"="D3"."ID")
4 - access("FTABLE"."D1"="D1"."ID")
6 - access("FTABLE"."D2"="D2"."ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
521 consistent gets
0 physical reads
0 redo size
139738 bytes sent via SQL*Net to client
4798 bytes received via SQL*Net from client
400 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5980 rows processed
set autotrace off
Exemple 1 : MVs avec GROUP BY, JOIN, REFRESH FAST et imbriquées
Disons pour débuter que l’on veuille transformer le SELECT du schéma d’exemple précédent en en vue matérialisée (MV). L’opération est assez directe, il suffit de lancer l’ordre ci-dessous
create materialized view mv1
build immediate refresh force as
select d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;
Materialized view created.
exec dbms_stats.gather_table_stats(user, 'MV1')
Faites juste attention de mettre des alias pour vos colonnes. Non pas que ce soit obligatoire mais pour simplifier l’utilisation de la vue. Votre première requête devient celle ci-dessous et vous l’évolution du plan ne vous surprendra pas :
set autotrace traceonly
select *
from mv1;
Execution Plan
----------------------------------------------------
Plan hash value: 2827454174
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------ ----------------------------------
| 0 | SELECT STATEMENT | | 5980 | 8 |
| 1 | MAT_VIEW ACCESS FULL| MV1 | 5980 | 8 |
----------------------------------------------------
Statistics
----------------------------------------------------
0 recursive calls
0 db block gets
421 consistent gets
0 physical reads
0 redo size
186181 bytes sent via SQL*Net to client
4798 bytes received via SQL*Net from client
400 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5980 rows processed
Exemple 1 – Evolution #1 : Rendre la vue « Fast Refreshable »
La première question qu’il est sans doute légitime de se poser (Quoique j’ai vu des cas ou un « Refresh » complet était plus efficace d’un « Refresh » incrémental), c’est : Est-ce que cette vue peut être mise à jour de manière incrémentale ? Si non, comment la rendre « REFRESH FAST ». Pour répondre à cette première question, vous pouvez sans doute compter sur la chance comme ci-dessous :
drop materialized view mv1;
create materialized view mv1
build immediate refresh fast as
select d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;
from d1,d2,d3,ftable
*
ERROR at line 7:
ORA-12015: cannot create a fast refresh materialized view from a complex query
Vous risquez de bloquer. Vous pouvez également utiliser DBMS_MVIEW.EXPLAIN_MVIEW comme ci-dessous…
D’abord, créez la table MV_CAPABILITIES_TABLE qui stockera les informations quant aux raisons qui empêchent une vue matérialisée d’être REFRESH FAST à l’aide du script utlxmv.sql comme ci-dessous :
@?/rdbms/admin/utlxmv
desc MV_CAPABILITIES_TABLE
Name Null? Type
----------------------- -------- ----------------
STATEMENT_ID VARCHAR2(30)
MVOWNER VARCHAR2(30)
MVNAME VARCHAR2(30)
CAPABILITY_NAME VARCHAR2(30)
POSSIBLE CHAR(1)
RELATED_TEXT VARCHAR2(2000)
RELATED_NUM NUMBER
MSGNO NUMBER(38)
MSGTXT VARCHAR2(2000)
SEQ NUMBER
Ensuite, utilisez DBMS_MVIEW.EXPLAIN_MVIEW (RTFM Data Warehouse Guide et PL/SQL Packages and Types Reference) pour connaître les raisons qui empêchent votre vue matérialisée d’être REFRESH FAST. Voici un exemple d’utilisation avec la vue précédente :
var mv char(2000)
begin
:mv:='create materialized view mv1
build immediate refresh fast as
select d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1';
end;
/
truncate table MV_CAPABILITIES_TABLE;
exec DBMS_MVIEW.EXPLAIN_MVIEW (:mv)
Enfin, affichez le résultat, stocke dans la table créée précédemment :
col REL_TEXT format a15
col MSGTXT format a70
SELECT capability_name,
possible,
SUBSTR(related_text,1,15)
AS rel_text,
SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
where capability_name not like '%PCT%'
and capability_name not like '%REWRITE%'
ORDER BY seq;
CAPABILITY_NAME P REL_TEXT MSGTXT
----------------------------- - ------------ ------------------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N aggregate function nested within an expression
REFRESH_FAST_AFTER_INSERT N SCOTT.FTABLE the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
Le vrai travail commence alors, à force de ce qui suit et à force d’itérations, vous arriverez a construire une vue matérialisée REFRESH FAST qui réponde à vos besoins :
- Ajoutez des MV logs à vos tables de base
- Ne mettre aucune expression à l’extérieur des fonctions d’agrégation
- Si votre select contient SUM(expr), ajoutez COUNT(expr)
- Votre select doit contenir COUNT(*)
Et remarquez qu’il est possible de construire des vues materialisées REFRESH FAST avec des jointures et des agrégats. Pour plus de détails à propos des restrictions associées au mode REFRESH FAST, regardez les differentes sections « Restrictions on Fast Refresh du Chapitre 6 d’Oracle Data Warehousing Guide 11g« .
create materialized view log on ftable
with sequence, rowid (d1,d2,d3,val1)
including new values;
create materialized view log on d1
with sequence, rowid (id,level2)
including new values;
create materialized view log on d2
with sequence, rowid (id,level1)
including new values;
create materialized view log on d3
with sequence, rowid (id,level1)
including new values;
begin
:mv:='create materialized view mv1
build immediate refresh fast as
select count(*) rowcounts,
d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
count(ftable.val1/1000) cmetric1,
sum(ftable.val1/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1';
end;
/
truncate table MV_CAPABILITIES_TABLE;
exec DBMS_MVIEW.EXPLAIN_MVIEW (:mv)
col REL_TEXT format a15
col MSGTXT format a70
SELECT capability_name,
possible,
SUBSTR(related_text,1,15)
AS rel_text,
SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
where capability_name not like '%PCT%'
and capability_name not like '%REWRITE%'
ORDER BY seq;
CAPABILITY_NAME P REL_TEXT
----------------------------- - ---------
REFRESH_COMPLETE Y
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
Vous direz que ce n’est pas tout à fait ce que vous vouliez ? Ajoutez simplement une vue qui fait les derniers calculs sur la MV et vous avez ce que vous voulez…
create materialized view mv1
build immediate
refresh fast as
select count(*) rowcounts,
d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
count(ftable.val1/1000) cmetric1,
sum(ftable.val1/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;
Materialized view created.
exec dbms_stats.gather_table_stats(user, 'MV1');
create view v1 as
select d1_level2,
d2_level1,
d3_level1,
trunc(metric1) metric1
from mv1;
View created.
Exemple 1 – Evolution #2 : Comprendre ce que fait le mode « Fast Refreshable »
La deuxième question assez légitime à propos du REFRESH FAST, c’est de savoir ce que fait Oracle sur ce type de vues materialisées à chaque fois que vous faites un REFRESH. Je n’ai pas l’intention d’être exhaustif sur le sujet, mais au moins, voici ce que vous devrez faire pour en savoir plus. Regardons le cas ou la vue matérialisée est mise à jour sur les COMMIT :
drop view v1;
drop materialized view mv1;
create materialized view mv1
build immediate refresh fast
on commit as
select count(*) rowcounts,
d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
count(ftable.val1/1000) cmetric1,
sum(ftable.val1/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;
exec dbms_stats.gather_table_stats(user, 'MV1');
Pour savoir ce que fait Oracle, activez sql_trace, faites un update/commit de la table FTABLE par exemple et desactivez sql_trace. Utilisez tkprof pour repérer les ordres SQL générés en arrière plan associés à votre update/commit. Voilà ce que ca donne avec 11.1 :
sqlplus / as sysdba
show parameter user_dump_dest
connect scott
alter session set sql_trace=true
update FTABLE set val1=3
where (d1, d2, d3)
in (select d1,d2,d3
from ftable
where rownum<=2);
commit;
alter session set sql_trace=false;
exit
cd /u01/app/oracle/diag/rdbms/redx/REDX/trace
tkprof REDX_ora_9361.trc REDX_ora_9361.tkprof
explain=scott/tiger aggregate=no sys=no
Vous pouvez ainsi facilement capturer l’ordre qui met à jour la vue matérialisée :
merge INTO "SCOTT"."MV1" "SNA$" USING
(SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=598) */
"MAS$3"."LEVEL2" "GB0",
"MAS$2"."LEVEL1" "GB1",
"MAS$1"."LEVEL1" "GB2",
nvl(SUM(decode("DLT$0"."DML$$", 'I', 1,-1)
*("DLT$0"."VAL1" / 1000)),0) "D0",
SUM(decode("DLT$0"."DML$$",'I',1,-1)
*decode(("DLT$0"."VAL1" / 1000),NULL,0,1)) "D1",
SUM(decode("DLT$0"."DML$",'I',1,-1)) "D2"
FROM
(SELECT chartorowid("MAS$"."M_ROW$") rid$,
"MAS$"."D1",
"MAS$"."D2",
"MAS$"."D3",
"MAS$"."VAL1",
decode("MAS$".old_new$,'N','I','D') dml$
FROM "SCOTT"."MLOG$_FTABLE" "MAS$"
WHERE "MAS$".snaptime$ > :1)
"DLT$0",
(SELECT "MAS$"."ROWID" "RID$",
"MAS$"."LEVEL1",
"MAS$"."ID"
FROM "SCOTT"."D3" "MAS$")
AS OF snapshot(:2) "MAS$1",
(SELECT "MAS$"."ROWID" "RID$",
"MAS$"."LEVEL1",
"MAS$"."ID"
FROM "SCOTT"."D2" "MAS$")
AS OF snapshot(:2) "MAS$2",
(SELECT "MAS$"."ROWID" "RID$",
"MAS$"."LEVEL2",
"MAS$"."ID"
FROM "SCOTT"."D1" "MAS$")
AS
OF snapshot(:2) "MAS$3"
WHERE("DLT$0"."D1" = "MAS$3"."ID"
AND "DLT$0"."D2" = "MAS$2"."ID"
AND "DLT$0"."D3" = "MAS$1"."ID")
GROUP BY "MAS$3"."LEVEL2",
"MAS$2"."LEVEL1",
"MAS$1"."LEVEL1") "AV$"
ON (sys_op_map_nonnull("SNA$"."D1_LEVEL2")
= sys_op_map_nonnull("AV$"."GB0")
AND sys_op_map_nonnull("SNA$"."D2_LEVEL1")
= sys_op_map_nonnull("AV$"."GB1")
AND sys_op_map_nonnull("SNA$"."D3_LEVEL1")
= sys_op_map_nonnull("AV$"."GB2"))
WHEN matched THEN
UPDATE
SET "SNA$"."METRIC1" =
decode("SNA$"."CMETRIC1" +
"AV$"."D1",0,NULL,nvl "SNA$"."METRIC1",0) +
"AV$"."D0"),
"SNA$"."CMETRIC1" = "SNA$"."CMETRIC1" + "AV$"."D1",
"SNA$"."ROWCOUNTS" = "SNA$"."ROWCOUNTS" + "AV$"."D2"
DELETE
WHERE("SNA$"."ROWCOUNTS" = 0)
WHEN NOT matched THEN
INSERT("SNA$"."D1_LEVEL2","SNA$"."D2_LEVEL1","SNA$"."D3_LEVEL1",
"SNA$"."METRIC1","SNA$"."CMETRIC1","SNA$"."ROWCOUNTS")
VALUES("AV$"."GB0","AV$"."GB1","AV$"."GB2",
decode("AV$"."D1", 0, NULL, "AV$"."D0"), "AV$"."D1", "AV$"."D2")
WHERE("AV$"."D2" > 0)
Vous pouvez remarquer dans cet ordre que seule la ligne correspondante dans MV1 est mise à jour et que (Cf section UPDATE) cette mise à jour est obtenue en ajoutant la variation et non par par un recalcul complet. Vous pouvez procéder ainsi pour repondre à d’autres questions si vous le souhaitez.
Exemple 1 – Evolution #3 : Mettre en cascade une seconde vue matérialisée également « REFRESH FAST »
Si maintenant vous voulez créer une vue matérialisée avec un niveau d’agrégation plus important, vous pouvez toujours la baser sur la vue matérialisée que vous venez de créer ; Il est en effet possible de mettre en cascade des vues matérialisées (Nested Materialized View) et que celles-ci restent REFRESH FAST. Par exemple, supposons qu’il faille maintenant créer une vue matérialisée basée sur le SELECT qui suit :
select d2_level1,
d3_level1,
avg(trunc(metric1)) ametric1
from mv1
group by d2_level1, d3_level1;
Si votre vue matérialisée respecte les règles pour être REFRESH FAST, c’est à dire dans ce cas :
- La table de base (Ici MV1) à le bon « Materialized View Log » pour permettre à la vue d’être REFRESH FAST
- Pas d’expression à l’extérieur de les fonctions d’agrégation
- Si AVG(expr), inclure COUNT(expr)
- La vue contient COUNT(*)
Il sera possible de créer encore une vue matérialisée REFRESH FAST. Noter que dans l’exemple ci-dessous, elle est créée avec un REFRESH sur les COMMIT mais que souvent, un REFRESH périodique est plus adapté ; posez-vous bien la question :
create materialized view log on mv1
with sequence, rowid
(d2_level1, d3_level1, metric1)
including new values;
create materialized view mv2
build immediate
refresh fast on commit as
select d2_level1,
d3_level1,
count(*) cstar,
count(trunc(metric1)) cmetric1,
avg(trunc(metric1)) ametric1
from mv1
group by d2_level1, d3_level1;
Materialized view created.
Voilà pour ce premier exemple. Notez qu’il y a de nombreuses limites à une telle approche en cascade; la complexité de mise en œuvre bien sur. Mais aussi l’impact des vues matérialisées sur ordre DML en terme de performance, surtout si le REFRESH est ON COMMIT ! Enfin, si vous pensez toujours faire vos refresh ON COMMIT n’oubliez pas qu’un changement sur les structures sous-jascentes (Un ordre DDL en général et à quelques exceptions prêt, cf PARTITION CHANGE TRACKING), invalidera les vues matérialisées et empêchera vos prochains ordres DML à moins que vous ne rafraîchissiez avec, par exemple, DBMS_MVIEW.REFRESH(‘MV1′,’C’) les vues.
En outre, si la seconde vue matérialisée MV2 était une somme et non une moyenne, il est alors possible d’utiliser les GROUPING SET pour « mettre le contenue de la seconde vue matérialisée dans la première ». C’est le sujet du second exemple qui suit…
Exemple 2 : MV avec Grouping Set
En substance cet ex
emple s’appuie sur le fait qu’on peut faire une somme dans un SELECT avec plusieurs niveaux d’agrégation dans le résultat. Par exemple, on peut calculer en même temps la somme des VAL1 de FTABLE agrégés sur, d’une part : d1_level2, d2_level1 et d3_level1 et d’autre part sur d2_level1 et d3_level1. Pour cela, il suffit d’utiliser ROLLUP dans la clause GROUP BY comme ceci :
select d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d2.level1,d3.level1, rollup(d1.level2);
Le principe est essentiellement le même que pour une vue matérialisée avec des agrégats. S’ajoute simplement la nécessité d’utiliser, dans la clause SELECT la fonction GROUPING sur les colonnes incluses dans le GROUP BY (Qu’elles soient dans les clauses ROLLUP, CUBE ou pas !). Pour plus d’information sur ces restrictions, reportez vous encore au manuel « Restrictions on Fast Refresh on Materialized Views with Aggregates ». Vous constaterez, si vous avez laissé les Materialized View Log créés précédemment, vous pouvez créer MV3 comme ci-dessous :
drop materialized view mv3;
create materialized view mv3
build immediate
refresh fast as
select d1.level2 d1_level2,
d2.level1 d2_level1,
d3.level1 d3_level1,
grouping(d1.level2) glevel_d1,
grouping(d2.level1) glevel_d2,
grouping(d3.level1) glevel_d3,
count(ftable.val1) cmetric1,
count(*) cstar,
sum(ftable.val1) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d2.level1,d3.level1, rollup(d1.level2);
Pour sélectionner le niveau d’agrégat sur d2.level1 et d3.level1, le SELECT sur la vue matérialisée ne doit alors pas contenir de SUM mais juste un critère de sélection sur glevel_d1, glevel_d2 et glevel_d3, comme ci-dessous :
select d2_level1,
d3_level1,
metric1
from mv3
where glevel_d1=1
and glevel_d2=0
and glevel_d3=0;
Vous utiliserez également ce critère avec d’autres valeurs , pour le niveau sur d1_level2, d2_level1 et d3_level1 :
select d1_level2,
d2_level1,
d3_level1,
metric1
from mv3
where glevel_d1=0
and glevel_d2=0
and glevel_d3=0;
Et voilà qui termine ce second exemple, vous pouvez facilement l’enrichir en ajoutant les niveaux d’agrégat de votre choix dans la vue résultantes.
Avant d’en finir, supprimez les objets de cet exemple de votre base de données
drop materialized view mv3;
drop materialized view mv2;
drop view v1;
drop materialized view mv1;
drop table ftable purge;
drop table d1 purge;
drop table d2 purge;
drop table d3 purge;
drop table MV_CAPABILITIES_TABLE purge;
Pour conclure
Vous voyez où je veux en venir, non ? Les cubes MV de 11g… et c’est déjà une autre histoire
