Si vous avez suivi l’article précédent intitulé « Mon petit Data Mart (Partie 1) : Dimensions, Partitions et Données« , vous disposez maintenant d’une base de données 11g Release 2 avec une table de fait et 4 tables de dimensions. Nous allons maintenant utiliser ce schéma pour illustrer l’utilisation et l’intérêt des vues matérialisées et du query rewrite…
Un calcul de somme
Commençons par une requête simple qui permet de calculer la somme des colonnes metric1 groupées par an. Comme vous pouvez le voir, dans mon cas ci-dessous, le temps d’exécution (en 11.2.0.1 sur mon laptop sous Linux), il faut à peu près 20 secondes pour exécuter la requête :
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
set timing on
set num 12
select trunc(t.month_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by trunc(t.month_id,'YYYY')
order by 1;
YEAR M1
--------- ------------
01-JAN-09 10248074782
01-JAN-10 170331336
Elapsed: 00:00:20.01
Vue matérialisée et query rewrite
Pour vous montrer l’intérêt d’une vue matérialisée, vous allons créée une vue (sans toutefois que celle-ci soit rafraichie automatiquement) et nous arranger pour que la requête puisse l’utiliser. Dans notre cas, nous allons simplement aggréger la table FACT au niveau mois de l’axe temps (sans toucher les autres niveaux). Voilà la vue matérialisée associée:
drop materialized view ag_fact_by_month;
create materialized view ag_fact_by_month
partition by range(month_id) interval(NUMTOYMINTERVAL(1,'MONTH'))
subpartition by hash(dim2_id) subpartitions 2
(partition p0 values less than (to_date('01/01/2009','DD/MM/YYYY')))
nologging
enable query rewrite
as select t.month_id,
f.dim2_id,
f.dim3_id1,
f.dim3_id2,
f.dim4_id,
sum(f.metric1) metric1_sum,
count(f.metric1) metric1_count,
avg(f.metric1) metric1_avg,
sum(f.metric2) metric2_sum,
count(f.metric2) metric2_count,
avg(f.metric2) metric2_avg
from fact f, time t
where f.time_id=t.time_id
group by t.month_id, f.dim2_id,
f.dim3_id1, f.dim3_id2, f.dim4_id;
Elapsed: 00:00:35.62
select count(*)
from ag_fact_by_month;
COUNT(*)
--------
15600
Si vous relancez la requête précédente, vous constaterez que les temps d’exécution de la requête sont radicalement transformés et, que le plan d’exécution utilise notre vue matérialisée :
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
set timing on
set num 12
select trunc(t.month_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by trunc(t.month_id,'YYYY')
order by 1;
YEAR M1
--------- ------------
01-JAN-09 10248074782
01-JAN-10 170331336
Elapsed: 00:00:00.03
select *
from table(dbms_xplan.display_cursor(
format=>'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select trunc(t.month_id,'YYYY') year, sum(f.metric1) m1 from
fact f, time t where t.time_id=f.time_id group by
trunc(t.month_id,'YYYY') order by 1
Plan hash value: 2405448810
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | HASH GROUP BY | |
| 3 | MAT_VIEW REWRITE ACCESS FULL| AG_FACT_BY_MONTH |
-----------------------------------------------------------
Consistence et Query Rewrite
Lorsque vous modifiez les données de la table de faits, la vue matérialisée n’est plus utilisable dans les conditions normales. L’exemple qui suit illustre les 2 méthodes qui permettent d’utiliser la vue matérialisée de nouveau à savoir (1) l’utilisation du paramètre query_rewrite_integrity=stale_tolerated ou (2) le refresh de la vue matérialisée :
insert into fact
values (to_date('06/01/2010','DD/MM/YYYY'),
2, 2, 1, 'AXIS 2',
'XX', 'XS3', 7,
to_date('11/01/2010','DD/MM/YYYY'),
17, 22);
commit;
set timing on
set num 12
select trunc(t.month_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by trunc(t.month_id,'YYYY')
order by 1;
YEAR M1
--------- ------------
01-JAN-09 10248074782
01-JAN-10 170331353
Elapsed: 00:00:19.88
alter session set query_rewrite_integrity=stale_tolerated;
select trunc(t.month_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by trunc(t.month_id,'YYYY')
order by 1;
YEAR M1
--------- ------------
01-JAN-09 10248074782
01-JAN-10 170331336
Elapsed: 00:00:00.02
exec dbms_mview.refresh('ag_fact_by_month','complete')
alter session set query_rewrite_integrity=enforced;
select trunc(t.month_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by trunc(t.month_id,'YYYY')
order by 1;
YEAR M1
--------- ------------
01-JAN-09 10248074782
01-JAN-10 170331353
Elapsed: 00:00:00.02
Et ensuite ?
Si l’utilisation du niveau aggrégé (i.e. time.month_id) passe par la vue matérialisée, il n’en est rien si vous interrogez un autre niveau (e.g. time.year_id), comme vous pouvez vous en rendre compte ci-dessous :
select to_char(t.year_id,'YYYY') year,
sum(f.metric1) m1
from fact f, time t
where t.time_id=f.time_id
group by to_char(t.year_id,'YYYY')
order by 1;
YEAR M1
---- ------------
2009 10248074782
2010 170331353
Elapsed: 00:00:20.94
select *
from table(dbms_xplan.display_cursor(
format=>'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select to_char(t.year_id,'YYYY') year, sum(f.metric1) m1 from
fact f, time t where t.time_id=f.time_id group by
to_char(t.year_id,'YYYY') order by 1
Plan hash value: 2313903028
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | PARTITION RANGE ALL | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TIME |
| 5 | PARTITION HASH ALL| |
| 6 | TABLE ACCESS FULL| FACT |
--------------------------------------
Dans notre prochain article, nous explorerons l’utilisation des dimensions pour répondre à ce besoin.
