Discussion :

[aline]

Bonjour,

Je cherche à créer une vue matérialisée qui est la jointure de deux tables et de lui appliquer un rafraichissement fast.
pour l'instant, toutes mes tentatives ont échoué.

voici le code très simple de création des tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create table emp(noemp number, nodept number);
 
alter table EMP
  add constraint emp_pk primary key (NOEMP);
 
 
create table dept(nodept number, noregion number);
 
alter table DEPT
  add constraint dept_pk primary key (nodept);
 
alter table EMP
  add constraint EMP_FK_DEPT foreign key (NODEPT)
  references dept (NODEPT);

J'ai fait deux tests pour la creation des journaux de vues matérialisées

1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
 
create materialized view log on emp;
 
create materialized view log on dept;

2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
 
create materialized view log on emp with rowid,primary key;
 
create materialized view log on dept with primary key,rowid;

voici les codes d'erreurs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
 
create materialized view mv_emp_by_region 
enable query rewrite 
as
select count(*), noregion 
from emp, dept
where emp.nodept=dept.nodept
group by noregion;
 
 
call dbms_mview.refresh('mv_emp_by_region','f')

1:
ORA-12032: cannot use rowid column from materialized viex log on dept


2:
ORA-32401: materialized view log dept does not have new values

Je catche une 32401 ce qui m'étonne pour deux raisons:
1: et alors, ou est le problème s'il n'y a pas de nouvelles valeurs.
2: il y a de nouvelles valeurs, j'en ai rajouté et ait retesté le rafraichissement et retombe sur la même erreur!!!!!!!



Une idée please

[rouardg]

Bonjour Aline,

La doc Oracle précise pour ces 2 erreurs :

ORA-12032 cannot use rowid column from materialized view log on "string"."string"

Cause: The materialized view log either does not have ROWID columns logged, or the timestamp associated with the ROWID columns is more recent than the last refresh time.

Action: A complete refresh is required before the next fast refresh. Add ROWID columns to the materialized view log, if required.

ORA-32401 materialized view log on "string"."string" does not have new values

Cause: The materialized view log on the indicated table does not have new values information.

Action: Add new values to the materialized view log using the ALTER MATERIALIZED VIEW LOG command.

A priori, il faut un rafraichissement complet la 1ère fois, en appelant comme tu l'as fait call dbms_mview.refresh('mv_emp_by_region','f'),
mais pas avec la lettre 'f' pour fast refresh, mais avec 'c' pour un complete refresh.

[aline]

Bonjour Rouardg,


En effet, je peux faire autant de rafraichissement complet que je veux.
Mais des que je pousse l'option fast, je catche l'erreur 1 ou 2 suivant ma configuration.

Donc, il doit y avoir une erreur dans mon code quelque part, mais je ne comprends pas ou. J'imagine seulement que cela vient de mes jouranux de journalisations qui sont mal configurés.

tout d'abord, l'ereur 12032 me semble halucinante.
en effet, pourquoi utiliser la rowid si une pk est définie. J'imaginai que cette option était possible uniquement s'il n'y avait pas de pk sur la table. Je préfererai ne pas faire tourner mes journaux avec sauf si c'est obligatoire, sinon j'aimerai comprendre.


Et enfin la deuxième erreur me parrait tout aussi surprenante puisque je l'ai même quand j'insère de nouvelles valeurs.

[aline]

Bonjour,

Je relance ce post qui n'a pas inspiré les foules


J'essaye juste de comprendre comment fonctionne le rafraichissement fast de vue matérialisées comprenant des jointures.
a mon avis, les erreurs viennnent de la mauvaise compréhension et donc mauvaise utilsation (et ce n'est pas faute d'avoir potassé cette maudite doc )
des materialized view log et plus particulièrement de quelles colonnes y inclure.
voici un petit script de test.
Les rafraichissement de types complets marchent très bien, mais pas les fasts.

J'immagine pourtant que beaucoup d'entres vous ont déjà utilisé ce genre de choses et que cela ne doit pas être trop compliqué.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
 
>drop materialized view MV_EMP_BY_REGION;
 
Materialized view dropped
 
>drop table emp;
Table dropped
 
>drop table dept;
Table dropped
 
>create table emp(noemp number, nodept number);
Table created
 
>alter table EMP  add constraint emp_pk primary key (NOEMP);
Table altered  
 
>create table dept(nodept number, noregion number);
Table created
 
>alter table DEPT  add constraint dept_pk primary key (nodept);
  Table altered
 
>alter table EMP
  add constraint EMP_FK_DEPT foreign key (NODEPT)
  references dept (NODEPT);
Table altered
 
>insert into dept values(1,100);
>insert into emp values(1,1);
.......
>commit;
Commit complete
 
 
>select count(*), noregion 
from emp, dept
where emp.nodept=dept.nodept
group by noregion;
 
  COUNT(*)   NOREGION
---------- ----------
        60        100
        40        200
        50        300
       100        400
 
 
>create materialized view log on emp with rowid,primary key;
Materialized view log created
 
>create materialized view log on dept with primary key,rowid;
Materialized view log created
 
>create materialized view mv_emp_by_region 
enable query rewrite 
as
select count(*), noregion 
from emp, dept
where emp.nodept=dept.nodept
group by noregion;
Materialized view created
 
 
--call dbms_mview.refresh('mv_emp_by_region','c');
 
 
 
>insert into dept values(83000,20000);
........
Commit complete
 
>call dbms_mview.refresh('mv_emp_by_region','f');
ORA-32401: materialized view log on "DEPT" does not have new values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

[rouardg]

Bonjour Aline,

J'ai résolu ton pb, mais en fait ne me demande pas trop comment.

Voici ce que j'ai créé en 9i et qui marche :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create materialized view log on emp with rowid, (nodept) including new values ;
 
create materialized view log on dept with rowid, (noregion) including new values ;
 
create materialized view mv_emp_by_region
refresh fast on commit  
enable query rewrite 
as 
select count(*), noregion 
from emp, dept 
where emp.nodept=dept.nodept 
group by noregion; 
 
call dbms_mview.refresh('mv_emp_by_region','c') ;

Je t'avoue n'avoir fait que très très peu de vues matérialisées. Pour qu'elle marche, j'ai passé un certain temps dans la doc Oracle, notamment le 'Data Warehousing Guide' au chapitre 8.

Mais j'avoue ne pas avoir tout saisi, et qu'il me faudrait qq jours pour tester plusieurs configurations de VM, et en tiré ma propre expérience, tellement il y a de possibilités, de syntaxe, et de restrictions.

Sinon bonnes fêtes !

[aline]

Bonjour Rouardg,

Une très bonne anée à toi ainsi qu'à tous les membres de notre petite communauté Oracle.

Concernant maintenant le fonctionnement de notre petite histoire, le fonctionnement des journaux de vues matérialisés me semble très mal documenté et assez complexe finalement (pour quelque chose qui devrait être très simple...). tu as trouvé un exemple qui marche très bien que j'ai pu dériver, mais il nous manques toujours la substantifique moelle (je sais, je suis très pointilleuse )!


J'ai vu dans le peux de docs que j'ai trouvé que Oracle recommande par exemple sur des jointures de rajouter une sequence, sinon on pourrait avoir des problèmes d'integrités (sans donner d'exemples concrets). Je l'ai fait, mais la séquence ne supporte pas le rafrraichissement de vues simples!

Donc les sequences, c'est bien quand ca marche...
De plus, il ne me parrait pas évident de savoir quelles colonnes rajouter dans les journaux de logs.


et voici un petit exemple de ce que je raconte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
SQL> create materialized view log on emp with rowid, sequence (nodept) including new values ;
 
Materialized view log created
 
SQL> create materialized view log on dept with rowid, sequence (nodept, noregion) including new values ;
 
Materialized view log created
 
 
SQL> create materialized view mv_emp_by_region
  2  build immediate
  3  --refresh fast on commit
  4  enable query rewrite
  5  as
  6  select count(*), noregion
  7  from emp, dept
  8  where emp.nodept=dept.nodept
  9  group by noregion;
 
Materialized view created
 
SQL> create materialized view mv_emp
  2  enable query rewrite
  3  as
  4  SELECT * FROM EMP;
 
Materialized view created
 
SQL> call dbms_mview.refresh('mv_emp_by_region','f');
 
Method called
 
SQL> call dbms_mview.refresh('mv_emp','f');
 
call dbms_mview.refresh('mv_emp','f')
 
ORA-12031: cannot use primary key columns from materialized view log on EMP
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

et pour finir, je ne trouve pas clair de savoir quelles colonnes ajouter dans la table des journaux. Oracle recommande par exemple de mettre la colonne de jointure.
dans ce cas, pourquoi cela marche si on ne le fait pas comme dans notre exemple (nodept ne fait pas parti du journal de la table dept))?



en conclusion de ce post, je ne suis peux être pas très futfute, mais je n'ai pas l'impression que beaucoup de monde maitrise ce concept et Oracle a des progrès à faire à ce niveau!

[rouardg]

Bonjour Aline,

Après consultation de la doc Oracle, ainsi que d'un bouquin de dataware house, il en résulte que la création des VM (Vues Matérialisées) doivent se conformer à des tas de règles.

Plutôt que de lister toutes ces règles dans un document, Oralce a préféré faire un outil, qui te présente dans une table (MV_CAPABILITIES_TABLE) les possibilités d'une VM, ainsi que ce qui ne va pas.

J'ai repris ton exemple.

En prérequis, tu dois créer la table MV_CAPABILITIES_TABLE à l'aide du script UTLXMV.SQL qui est dans le répertoire ORACLE_HOME/rdbms/admin.

Ensuite, tu appelles la procédure EXPLAIN_MVIEW du package DBMS_MVIEW, en donnant ta requête SQL comme paramètre d'entrée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
begin
  dbms_mview.explain_mview ('SELECT * FROM EMP') ;
end ;
/

Pour finir, tu n'as plus qu'à faire un SELECT sur la table MV_CAPABILITIES_TABLE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select CAPABILITY_NAME, POSSIBLE P, RELATED_TEXT, RELATED_NUM, MSGNO, MSGTXT, SEQ from MV_CAPABILITIES_TABLE ;
 
 
CAPABILITY_NAME	P	RELATED_TEXT	RELATED_NUM	MSGNO	MSGTXT	SEQ
PCT	N					1
REFRESH_COMPLETE	Y					1002
REFRESH_FAST	N					2003
REWRITE	Y					3004
PCT_TABLE	N	EMP	14	2068	relation is not a partitioned table	4005
REFRESH_FAST_AFTER_INSERT	N	ROUARDG.EMP		2080	mv log must have primary key	5006
REFRESH_FAST_AFTER_ONETAB_DML	N			2146	see the reason why REFRESH_FAST_AFTER_INSERT is disabled	6007
REFRESH_FAST_AFTER_ANY_DML	N			2161	see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled	7008
REFRESH_FAST_PCT	N			2157	PCT is not possible on any of the detail tables in the materialized view	8009
REWRITE_FULL_TEXT_MATCH	Y					9010
REWRITE_PARTIAL_TEXT_MATCH	Y					10011
REWRITE_GENERAL	Y					11012
REWRITE_PCT	N			2158	general rewrite is not possible and PCT is not possible on any of the detail tables	12013

J'ai repris ton exemple. En particulier, le REFRESH_FAST n'est pas possible (3 ième ligne du select), car la 6 ième ligne t'indique la raison :
REFRESH_FAST_AFTER_INSERT mv log must have primary key

Voilà, j'espère t'avoir aider. Pour info, lorsque tu vois dans la table le terme PCT, cela signifie le 'Partition Change Tracking'. C'est une caractéristique de la 9i, et elle ne s'applique qu'aux VM construites sur des tables partitionnées, ce qui n'est pas ton cas.

En gros, lors d'une opération DML ou de maintenance sur une partition de la table, Oracle mémorise cet événement pour le répercuter sur la portion de la VM concernée, lors du rafraîchissement. Cette opération s'appelle le PCT refresh.

PS : ces infos proviennent du bouquin 'Oracle Database 10g data Warehousing', de Lilian Hobbs..., dans la collection ELSEVIER DIGITAL PRESS, si jamais tu es intéressée.