Dans les 2 articles précédents Mon petit Data Mart (Partie 1) : Dimensions, Partitions et Données et Mon petit Data Mart (Partie 2) : Vues Matérialisées et Query Rewrite, nous avons commencé à construire un SI décisionnel avec Oracle Database 11.2 et montré les capacités de ré-écriture du SQL en tirant parti des vues matérialisées… y compris lorsque les données ne sont pas rafraichies!
Continuons d’ajouter aux fonctionnalités de notre modèle en créant des contraintes sur lesquelles l’optimiseur Oracle peut s’appuyer pour résoudre nos requêtes; nous allons en particulier explorer les « dimensions » qui permettent d’assurer le lien entre des niveaux d’agrégats décrits dans une table de dimensions.
Une vue matérialisée inutilisée
Comme présenté à la fin de l’article précédent, malgré le fait que la somme de la colonne metric1 soit calculée au niveau mois dans la vue matérialisée, si vous interrogez la table fact en faisant une agrégation au niveau de l’année, la vue matérialisée n’est pas utilisée :
set timing on
set num 11
select t.year_id year,
sum (f.metric1) metric1
from fact f, time t
where f.time_id=t.time_id
group by t.year_id
order by t.year_id;
YEAR METRIC1
--------- -----------
01-JAN-09 10248074782
01-JAN-10 170331353
Elapsed: 00:00:19.13
Contrainte « Dimension »
Pour permettre l’utilisation de la vue matérialisée, nous pouvons ajouter une dimension comme ci-dessous :
drop dimension time_dim;
create dimension time_dim
level day is time.time_id
level month is time.month_id
level quarter is time.quarter_id
level year is time.year_id
hierarchy y_rollup (
day child of
month child of
quarter child of
year)
attribute day determines (date_lib)
attribute month determines (month_lib)
attribute month determines (quarter_lib)
attribute month determines (year_lib);
Une dimension est une contrainte qui n’est pas vérifiée par Oracle. Pour prendre en compte la contrainte, il faut donc dire à l’optimiseur que nous faisons confiance à la contrainte, même si Oracle ne l’a pas vérifiée avec la commande ci-dessous :
alter session set query_rewrite_integrity=trusted;
La requête peut alors être exécutée; elle utilisera la vue matérialisée :
select t.year_id year,
sum (f.metric1) metric1
from fact f, time t
where f.time_id=t.time_id
group by t.year_id
order by t.year_id;
YEAR METRIC1
--------- -----------
01-JAN-09 10248074782
01-JAN-10 170331353
Elapsed: 00:00:00.10
select * from table(
dbms_xplan.display_cursor(format=>'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select t.year_id year, sum (f.metric1) metric1 from fact f,
time t where f.time_id=t.time_id group by t.year_id order by
t.year_id
Plan hash value: 747418634
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | HASH JOIN | |
| 3 | PART JOIN FILTER CREATE | :BF0000 |
| 4 | VIEW | |
| 5 | HASH UNIQUE | |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS FULL | TIME |
| 8 | PARTITION RANGE JOIN-FILTER | |
| 9 | PARTITION HASH ALL | |
| 10 | MAT_VIEW REWRITE ACCESS FULL| AG_FACT_BY_MONTH |
-------------------------------------------------------------
Dimension non valide
Comme vous pouvez vous en rendre compte ci-dessous, la requête n’utilise pas la vue matérialisée si Oracle doit valider l’intégrité de la base de données :
alter session set query_rewrite_integrity=enforced;
select t.year_id year,
sum (f.metric1) metric1
from fact f, time t
where f.time_id=t.time_id
group by t.year_id
order by t.year_id;
YEAR METRIC1
--------- -----------
01-JAN-09 10248074782
01-JAN-10 170331353
Elapsed: 00:00:18.99
Mais que se passe-t-il si une dimension n’est pas consistante ? Dans ce cas, le résultat sera faux comme vous pourrez le découvrir dans les 2 requêtes ci-dessous après avoir modifié les données de la table time :
update time set year_id=to_date('01/01/2010', 'DD/MM/YYYY'),
year_lib='2010'
where TIME_ID=to_date('31/12/2009', 'DD/MM/YYYY');
commit;
exec dbms_mview.refresh('AG_FACT_BY_MONTH','complete')
select t.year_id year,
sum (f.metric1) metric1
from fact f, time t
where f.time_id=t.time_id
group by t.year_id
order by t.year_id;
YEAR METRIC1
--------- -----------
01-JAN-09 10248074782
01-JAN-10 1037840410
Elapsed: 00:00:00.05
select t.year_id year,
sum (f.metric1) metric1
from fact f, time t
where f.time_id=t.time_id
group by t.year_id
order by t.year_id;
YEAR METRIC1
--------- -----------
01-JAN-09 10219840501
01-JAN-10 198565634
Elapsed: 00:00:19.14
Valider la cohérence de la dimension
Oracle offre une procédure DBMS_OLAP.VALIDATE_DIMENSION pour valider votre dimension. Pour l’utiliser, nous pouvons procéder comme suit :
truncate table dimension_exceptions;
exec dbms_olap.validate_dimension ('TIME_DIM', user, -
false, true);
col year_lib format a4
select time_id, year_id, year_lib from time
where rowid in (select bad_rowid
from dimension_exceptions)
order by 1;
TIME_ID YEAR_ID YEAR
--------- --------- ----
01-OCT-09 01-JAN-09 2009
02-OCT-09 01-JAN-09 2009
[...]
30-DEC-09 01-JAN-09 2009
31-DEC-09 01-JAN-10 2010
La dernière requête affiche l’ensemble des lignes qui ne vérifient pas la contrainte de dimension, à savoir l’ensemble des lignes du 3ème trimestre 2009. Ci-dessous, voici comment corriger cette dimension:
update time set year_id=to_date('01/01/2009', 'DD/MM/YYYY'),
year_lib='2009'
where TIME_ID=to_date('31/12/2009', 'DD/MM/YYYY');
commit;
exec dbms_mview.refresh('AG_FACT_BY_MONTH','complete')
truncate table dimension_exceptions;
exec dbms_olap.validate_dimension ('TIME_DIM', user, -
false, true);
col year_lib format a4
select time_id, year_id, year_lib
from time
where rowid in
(select bad_rowid
from dimension_exceptions)
order by 1;
no rows selected
Note
Malgré le fait que la dimension ait maintenant été validée, il est toujours nécessaire d’utiliser le paramètrequery_rewrite_integrity=trustedpour que la vue soit utilisée.
Et ensuite ?
Si l’utilisation des différents niveaux de la dimension peuvent maintenant être utilisés, il reste encore certains cas où la vue matérialisée pourrait être utilisée mais ne l’est pas. Voici u
n autre de ces cas :
select DIM2_ID id, sum(METRIC1)
from fact
group by DIM2_ID
order by 1;
ID SUM(METRIC1)
-- ------------
1 1040563094
2 1040793118
3 1041107486
4 1041441961
5 1041776436
6 1042120956
7 1041943136
8 1042713564
9 1042929508
10 1043016876
Elapsed: 00:00:19.14
select * from
table(dbms_xplan.display_cursor(format=>'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select DIM2_ID, sum(METRIC1) from fact group by DIM2_ID order by 1
Plan hash value: 3822482429
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | PARTITION RANGE ALL| |
| 3 | PARTITION HASH ALL| |
| 4 | TABLE ACCESS FULL| FACT |
-------------------------------------
Vous trouverez donc dans le prochain article comment les contraintes référentielles et not null peuvent, même inactives, permettre la ré-écriture de certaines autres requêtes.
