Cet article est le 4ème d’une série qui vise à explorer de manière itérative certains aspects d’un système d’information décisionnel avec Oracle Database 11.2. Voici une liste des articles précédents :
- Mon petit Data Mart (Partie 1) : Dimensions, Partitions et Données
- Mon petit Data Mart (Partie 2) : Vues Matérialisées et Query Rewrite
- Mon petit Data Mart (Partie 3) : Dimensions et Query Rewrite
.Dans ce nouvel article, nous allons voir comment les contraintes référentielles et les contraintes « not null » peuvent aider à améliorer les temps de réponse, même lorsqu’elles sont désactivées.
Contexte
Dans l’article précédent, nous nous étions arrêté sur le constat décevant que notre vue matérialisée n’était pas utilisée lorsque nous faisions une agrégation sur la-dite dimension mais sans faire la jointure avec la table correspondante; voici une requête typique qui illustre cette problématique :
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 |
-------------------------------------
Foreign Key et Not Null
Et de fait… Rien ne garantit jusqu’à présent que les valeurs de la colonne time_id dans la table fact soient bien incluses dans la table de dimension que nous avons nommée time ou même qu’elles ne soient pas null. Or, dans l’un ou l’autre des cas, l’agrégation de toutes les valeurs sur cet axe sera différent selon qu’on joint à la table ou non. Pour garantir que c’est le cas, il suffit donc de créer une contrainte référentielle entre les colonnes time_id de fact et de time. Nous ajouterons ensuite une contrainte « not null » sur fact.time_id; notre requête pourra alors utiliser la vue matérialisée :
alter table fact
add constraint fact_time_fk foreign key(time_id)
references time(time_id);
alter table fact modify (time_id not null);
set timing on
set num 11
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:00.02
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
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | PARTITION RANGE ALL | |
| 3 | PARTITION HASH ALL | |
| 4 | MAT_VIEW REWRITE ACCESS FULL| AG_FACT_BY_MONTH |
------------------------------------------------------------
Désactiver les contraintes
La requête est donc ré-écrite. Cependant, maintenir les contraintes peut être très contraignant pour nos besoins; nous pouvons donc les désactiver et, avec l’option rely, proposer au CBO de les utiliser malgré tout; la vue matérialisée sera dans ce cas toujours utilisée et ceux malgré la désactivation des contraintes :
col constraint_name format a15
select constraint_name, constraint_type
from user_constraints
where table_name='FACT';
CONSTRAINT_NAME C
--------------- -
FACT_TIME_FK R
SYS_C0011620 C
alter table fact modify constraint time_fk rely disable;
alter session set query_rewrite_integrity=enforced;
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:00.19
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
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | PARTITION RANGE ALL | |
| 3 | PARTITION HASH ALL | |
| 4 | MAT_VIEW REWRITE ACCESS FULL| AG_FACT_BY_MONTH |
------------------------------------------------------------
Note:
Vous remarquerez que l’on a pas à direquery_rewrite_integrity=trustedet que ça fonctionne, même si le paramètre est laissé àenforced.
Et ensuite ?
Notre petit système décisionnel commence à donner des temps de réponses intéressants dans de nombreux cas. Toutefois, lorsqu’on en vient à jouer avec les attributs ou de multiples dimensions au niveau du détail, c’est une autre histoire. Comment encore aller plus loin? c’est en substance le thème que nous aborderons dans le prochain article.
