Mon petit Data Mart (Partie 4) : Clés étrangères et Query Rewrite

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 :

.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 à dire query_rewrite_integrity=trusted et 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.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *