[9i] Trigger avec dbms_refresh

**deY!** · 16/03/2009, 16h55

Bonchour !

J'ai créé une grosse vue matérialisée avec ces paramètre

Code :

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

1
2
3
4
5
PARALLEL 3
BUILD IMMEDIATE
REFRESH ON DEMAND COMPLETE
WITH PRIMARY KEY
START WITH sysdate

Elle est construite avec une requete dite "complexe".
Pour la mettre à jour lorsque les tables constituant cette requête sont modifiées j'ai créé ce déclencheur

Code :

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

1
2
3
4
5
6
7
CREATE OR REPLACE TRIGGER "SCOTT"."TRIGGER_MATABLE" 
AFTER DELETE OR UPDATE ON "MATBLE"
DECLARE 
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	dbms_refresh.refresh('vm_marge_emetteur');
END;

Je n'ai pas d'erreur, mon soucis et l'utilisation forcé du pragma.
En effet, si je met à jour la table MATABLE, cette mise à jour n'est pas répercutée.

Les répercutions sont décalés, si je modifie cette même table, la mise à jour précédent est visible. Surement dû à la transaction autonome.

Comment pourrais-je avoir une propagation en temps réel sachant que je ne peux utilisé d'option ON UPDATE pour la VM.

**fatsora** · 16/03/2009, 17h37

Bonjour,

les mises a jour des vues materilaisées ne sont pas a faire par des triggers !!!

voir la doc officielle pour ca

moins tu mets de triggers dans tes bases mieux elles vont se porter ....

http://download.oracle.com/docs/cd/B...3.htm#i2098404

Code sql :

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
 
CREATE MATERIALIZED VIEW LOG ON ton_user.table1
tablespace TBS_USERS_LOGS
   WITH PRIMARY KEY
   INCLUDING NEW VALUES;
 
 
CREATE MATERIALIZED VIEW TOn_USER.MV_TABLE1                                                                                    
TABLESPACE TBS_USERS_DATA
BUILD DEFERRED
USING INDEX
TABLESPACE TBS_USERS_INDEX
REFRESH FAST ON DEMAND                                                                                                             
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT                                                                              
DISABLE QUERY REWRITE                                                                                                              
AS SELECT *  FROM Ton_USER.TABLE1       
;

apres la replissage

Code :

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

exec dbms_mview.refresh('TON_USER.MV_TABLE1','C') ;

mise a jour fast

Code :

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

exec dbms_mview.refresh('TON_USER.MV_TABLE1','F') ;

apres la proc de mise a jour toutes les 15 s ....

Code sql :

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

1
2
3
4
5
6
 
CREATE OR REPLACE PROCEDURE P_MV_TABLE1 as
begin
   dbms_mview.refresh('TON_USER.MV_TABLE1','F');
end;
/

ici on va utiliser le dbms_job, on peut faire avec le crontab toutes le 15 s...

Code sql :

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

1
2
3
4
5
6
7
 
DECLARE jobno number;
BEGIN
DBMS_JOB.SUBMIT(job => jobno,
what => 'P_MV_TABLE1;',
next_date => SYSDATE, interval => 'sysdate+1/5760'); END;
/

**deY!** · 17/03/2009, 09h02

Oui comme expliqué dans mon post, je ne peux faire de ON UPDATE et donc ni de FAST.

Je me suis tourné vers la solution des jobs. Cependant, il s'agit d'une grosse requête

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
CREATE MATERIALIZED VIEW vm_marge_emetteur
PARALLEL 3
BUILD IMMEDIATE
REFRESH ON DEMAND COMPLETE
WITH PRIMARY KEY
START WITH sysdate
AS
SELECT to_char(sum(vs.stock),'999G999G999G999G999D99','NLS_NUMERIC_CHARACTERS=''. ''') as STOCK, m.id_emetteur,
	decode(sum(vue.mtno),NULL,  (i.limite/1000000) - (i.encours_trader/1000), (i.limite/1000000) - (i.encours_trader/1000) - sum(vue.mtno)/1000000) as J,
	m.marge, m.a_afficher, m.emission as "O/N",
	to_char(m.s1fix,'FM9999D0099') as "S1FIX", to_char(m.s1var,'SFM9999D0099') as "S1VAR",
	to_char(m.s2fix,'FM9999D0099') as "S2FIX", to_char(m.s2var,'SFM9999D0099') as "S2VAR",
	to_char(m.s3fix,'FM9999D0099') as "S3FIX", to_char(m.s3var,'SFM9999D0099') as "S3VAR",
	to_char(m.m1fix,'FM9999D0099') as "M1FIX", to_char(m.m1var,'SFM9999D0099') as "M1VAR",
	to_char(m.m2fix,'FM9999D0099') as "M2FIX", to_char(m.m2var,'SFM9999D0099') as "M2VAR",
	to_char(m.m3fix,'FM9999D0099') as "M3FIX", to_char(m.m3var,'SFM9999D0099') as "M3VAR",
	to_char(m.m4fix,'FM9999D0099') as "M4FIX", to_char(m.m4var,'SFM9999D0099') as "M4VAR",
	to_char(m.m5fix,'FM9999D0099') as "M5FIX", to_char(m.m5var,'SFM9999D0099') as "M5VAR",
	to_char(m.m6fix,'FM9999D0099') as "M6FIX", to_char(m.m6var,'SFM9999D0099') as "M6VAR",
	to_char(m.m7fix,'FM9999D0099') as "M7FIX", to_char(m.m7var,'SFM9999D0099') as "M7VAR", 
	to_char(m.m8fix,'FM9999D0099') as "M8FIX", to_char(m.m8var,'SFM9999D0099') as "M8VAR",
	to_char(m.m9fix,'FM9999D0099') as "M9FIX", to_char(m.m9var,'SFM9999D0099') as "M9VAR",
	to_char(m.m10fix,'FM9999D0099') as "M10FIX", to_char(m.m10var,'SFM9999D0099') as "M10VAR", 
	to_char(m.m11fix,'FM9999D0099') as "M11FIX", to_char(m.m11var,'SFM9999D0099') as "M11VAR",
	to_char(m.m12fix,'FM9999D0099') as "M12FIX", to_char(m.m12var,'SFM9999D0099') as "M12VAR",
	to_char(m.s1bor,'SFM9999D0099') as "S1BOR", to_char(m.s2bor,'SFM9999D0099') as "S2BOR", to_char(m.m3bor,'SFM9999D0099') as "S3BOR",
	to_char(m.m1bor,'SFM9999D0099') as "M1BOR", to_char(m.m2bor,'SFM9999D0099') as "M2BOR", to_char(m.s3bor,'SFM9999D0099') as "M3BOR",
	to_char(m.m4bor,'SFM9999D0099') as "M4BOR", to_char(m.m5bor,'SFM9999D0099') as "M5BOR", to_char(m.m6bor,'SFM9999D0099') as "M6BOR",
	to_char(m.m7bor,'SFM9999D0099') as "M7BOR", to_char(m.m8bor,'SFM9999D0099') as "M8BOR", to_char(m.m9bor,'SFM9999D0099') as "M9BOR",
	to_char(m.m10bor,'SFM9999D0099') as "M10BOR", to_char(m.m11bor,'SFM9999D0099') as "M11BOR", to_char(m.m12bor,'SFM9999D0099') as "M12BOR",
	m.commentaire, (i.standar||' '||i.moodys||' '||i.fitch) as rating, to_char(date_modif,'dd/mm/yyyy hh24:mi:ss') as date_modif,
	sysdate As dateheure, (i.limite/1000000) as limite, (i.limite_trader/1000000) as limite_trader,
	decode(sum(vue.mtno), NULL, (i.limite_trader/1000000) - (i.encours_trader/1000), (i.limite_trader/1000000) - (i.encours_trader/1000) - sum(vue.mtno)/1000000) as DISPO_TRADER,
	decode(m.typetx, NULL, i.typetx, m.typetx) as typetx, decode(m.reftx, NULL, i.reftx, m.reftx) as reftx,
	decode(m.actualise, NULL, i.actualise, m.actualise) as actualise, decode(m.isdefault, NULL, 0, m.isdefault) as isdefault
FROM institution i
JOIN marge m ON i.id = m.id_emetteur
		AND to_char(m.date_modif,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
		AND i.id<>'*' AND m.a_afficher <>'2'
LEFT JOIN v_stock vs ON  m.id_emetteur = vs.institution_emetteur
LEFT JOIN (SELECT case when tyope = 'SP' then institution_sp else institution_emetteur end as institution_emetteur,
                                   case when (sens='V' AND tyope NOT IN ('SP')) then -mtno else mtno end as mtno
                            FROM operation, parametres p
			    WHERE p.id='date_trader' AND p.valeur_param IS NOT NULL
			    AND annul = '0'
                            AND ((sens = 'A'
                            AND tyope IN ('BT','CD','EU')
                            AND to_date(to_char(dope,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')
                            AND to_date(to_char(dfin,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy'))
                            OR (tyope IN ('SP')
                            AND to_date(to_char(dope,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')
                            AND to_date(to_char(dfin,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')
                            AND to_date(to_char(dfin,'DD/MM/YYYY'),'DD/MM/YYYY') - to_date(to_char(dval,'dd/mm/yyyy'),'DD/MM/YYYY') > 1)
                            OR (sens = 'V'
                            AND tyope IN ('BT','CD','EU')
                            AND to_date(to_char(dfin,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')
                            AND (to_date(to_char(dope,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')
                            OR  (to_date(to_char(dope,'DD/MM/YYYY'),'DD/MM/YYYY') <= to_date(valeur_param,'dd/mm/yyyy')
                            	AND to_date(to_char(dval,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(valeur_param,'dd/mm/yyyy')))))) vue ON i.groupe_trader = vue.institution_emetteur
GROUP BY vs.institution_emetteur, i.tyop, m.id_emetteur, i.limite, i.encours_trader, i.limite_trader, m.marge, m.emission,
	m.s1fix, m.s1var, m.s2fix, m.s2var, m.s3fix, m.s3var, m.m1fix, m.m1var, m.m2fix, m.m2var, m.m3fix, m.m3var,
	m.m4fix, m.m4var, m.m5fix, m5var, m.m6fix, m.m6var, m.m7fix, m.m7var, m.m8fix, m.m8var, m.m9fix, m.m9var,
	m.m10fix, m.m10var, m.m11fix, m.m11var, m.m12fix, m.m12var,
	m.s1bor, m.s2bor, m.s3bor, m.m1bor, m.m2bor, m.m3bor, m.m4bor, m.m5bor, m.m6bor, m.m7bor, m.m8bor, m.m9bor,
	m.m10bor, m.m11bor, m.m12bor, m.commentaire, m.a_afficher, i.standar, i.moodys, i.fitch, date_modif,
	m.typetx, m.reftx, m.actualise, i.typetx, i.reftx, i.actualise, m.isdefault
ORDER BY i.tyop desc, m.id_emetteur;

Quand je suis arrivé elle était encore plus grosse que ça, avec plus de sous-table. J'en ai retiré le maximum et utilisé des left join.
Quoi qu'il en soit, je ne peux pas me permettre de lancer un job toutes les 15sec à cause de la lourdeur de la requête.

Les triggers sont pour moi la solution idéal pour mettre à jour la vm qu'en cas de modification des informations la concernant.

Code :

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

1
2
3
4
BEGIN
  dbms_job.isubmit('3253', 'dbms_refresh.refresh(''vm_marge_emetteur'');', sysdate);
  COMMIT;
END;

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE TRIGGER "SCOTT"."TRIGGER_MARGE_MARGE_EMETTEUR" 
AFTER DELETE OR UPDATE ON "MARGE"
DECLARE
  p_heure NUMBER;
  p_min NUMBER;
BEGIN
  SELECT to_number(to_char(sysdate, 'hh24')) INTO p_heure FROM dual;
  SELECT to_number(to_char(sysdate, 'mi')) INTO p_min FROM dual;
  dbms_job.next_date('3253', trunc(sysdate)+ p_heure/24 + p_min/1440 + 1/86400);
END;

Les utilisateurs ont besoin de temps réel et sans ralentissement, je suis donc parti sur les vm, mais le temps réel en pâti.

Merci, pour ta réponse, je pense rester sur les jobs, mais je reste ouvert à toute autre proposition

[9i] Trigger avec dbms_refresh

PL/SQL Oracle

Discussions similaires

Partager

Partager