Discussion :

[jacquesh]

bonjour,

voila le problème

LA REQUETE

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select
  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4) DATE_TRANSPORT,
  TR_AGENT1 COD_AGT1,
  count(TR_AGENT1) NB_INTERV_A1,
  TR_AGENT2 COD_AGT2,
  count(TR_AGENT2) NB_INTERV_A2
from
  brancard
group by
  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4), TR_AGENT1, TR_AGENT2
;

Voila le resultat

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
 
DATE   COD_AGT1 NB_INTERV_A1   COD_AGT2 NB_INTERV_A2
---- ---------- ------------ ---------- ------------
2006          2            5                       0
2006          3            4          2            4
2006          3            1         11            1
2006          3           14                       0
2006          9            4          3            4
2006          9            1         10            1
2006          9            4         11            4
2006          9           16                       0
2006         10            1          2            1
2006         10            1                       0
2006         11            2          2            2
2006         11            7                       0
2006         13            1          2            1
2006         13            2         10            2
2006         13            1         11            1
2006         13            4                       0
2006         14            1         15            1
2006         14            6         16            6
2006         14            2         18            2
2006         14            5                       0
2006         15            2                       0
2006         16            1         15            1
2006         16            3         18            3
2006         16            4                       0
2006         17            1         14            1
2006         18            3         15            3
2006         18            3                       0
2006         20            1                       0
2006         23            1          9            1
2006         23            2                       0
2006         25           15                       0
2006         29            3         25            3
2006         29            1         27            1
2006         29            6                       0
2006                       0          9            1
2006                       0                       0
 
36 rows selected.

mais le resultat que je souhaite obtenir de facon 'élégante' c'est la fusion de la colonne COD_AGT1 & COD_AGT2 et respectivement NB_INTERV_A1 & NB_INTERV_A2...

une idée ??

merci d'avance.

[plaineR]

Je ne comprends pas ce que tu appelles fusion. Peux-tu nous mettre un exemple de ce que tu souhaites obtenir STP ?

[jacquesh]

en fait ce serai avoir :

Code :

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

1
2
3
4
5
6
7
8
9
DATE   COD_AGT1 NB_INTERV_A1
---- ---------- ------------
2006          2            5        
2006          3            4        
2006          3            1       
...
2006          2            4        /* Cette info était sur la 3ème et 4ème colonne*/
2006         11            1        /* Cette info était sur la 3ème et 4ème colonne*/
...

mais sans faire 2 requetes avec un UNION vue que cette table a plusieurs dizaines de milliers de lignes...

[plaineR]

mais sans faire 2 requetes avec un UNION

Je ne vois pourtant pas d'autres solutions

[Hugues_78]

Ta table fait plusieurs millions de lignes, mais le résultat, il a quel taille ?

Si ton résultat final est raisonablement petit, tu peux utiliser un with

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
 
WITH req AS
(SELECT
  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4) DATE_TRANSPORT,
  TR_AGENT1 COD_AGT1,
  count(TR_AGENT1) NB_INTERV_A1,
  TR_AGENT2 COD_AGT2,
  count(TR_AGENT2) NB_INTERV_A2
FROM
  brancard
GROUP BY
  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4), TR_AGENT1, TR_AGENT2
)
SELECT date_transport, cod_agt1, nb_interv_a1 from req
union all
select date_transport, cod_agt2, nb_interv_a2 from req
;

Si tu regarde le plan d'exécution, tu dois avoir un temp transformary table ou un truc du genre ==> il stocke le résultat intermédiaire dans le tablespace temp ==> c'est tout bon

[jacquesh]

WITH ?? sur que ca fonctionne sur une 8.1.7.4 ??

sinon en effet la table est très grande mais le resultat généré est tout petit (1 centaine de ligne au plus) ; le seul problème ce sont les 'count' - si j'en execute trop je crains que je ne bouffe trop de CPU -

[Hugues_78]

Oups... J'avais pas fait gaffe au 8.1.7... je suis pas sure pour le with

sinon, il y a toujours la possibilité de créer un snapshot avec le résultat de la requête, mais c'est moins dynamique.

Autre idée : gérer une mémoire cache via un package vu qu'il n'y a que quelques lignes.
Ce package (appelons le TOTO) aurait
- une fonction "select_en_cache" qui va faire la requête
- une fonction "purge_cache"
- une variable "derniere_requete" qui contient le code sql
- une variable "dernier_resultat" qui est le résultat de la requête (collection, varray ou autre)

(indulgence sur la syntaxe, ça fait un bout de temps que j'ai pas jouer avec les collections)

On va ensuite écrire le select comme suit :

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
 
SELECT date_transport, cod_agt1, nb_interv_a1 
FROM toto.select_en_cache('SELECT
	  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4) DATE_TRANSPORT,
	  TR_AGENT1 COD_AGT1,
	  count(TR_AGENT1) NB_INTERV_A1,
	  TR_AGENT2 COD_AGT2,
	  count(TR_AGENT2) NB_INTERV_A2
	FROM
	  brancard
	GROUP BY
	  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4), TR_AGENT1, TR_AGENT2'
	)
UNION ALL
SELECT date_transport, cod_agt1, nb_interv_a1 
FROM toto.select_en_cache('SELECT
	  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4) DATE_TRANSPORT,
	  TR_AGENT1 COD_AGT1,
	  count(TR_AGENT1) NB_INTERV_A1,
	  TR_AGENT2 COD_AGT2,
	  count(TR_AGENT2) NB_INTERV_A2
	FROM
	  brancard
	GROUP BY
	  SUBSTR(TO_CHAR(DT_TRANS,'YYYY'),1,4), TR_AGENT1, TR_AGENT2'
	)

La première branche va voir que la requête n'est pas encore exécuté et va donc remplir le varray puis retourner sa valeur. L'autre branche du union all va voir que la requête a déjà été faite, et va se contenter de retourner le varray.

Et quand on a fini, un petit

Code :

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

select purge_cache from dual

[jacquesh]

Ton idée me parrait très bien sur 2 points

1 - je n'ai encore jamais eu l'occasion d'utiliser la notion de package (donc ca me fera une excuse pour m'y coller )

2 - Cet histoire de cache me parait performant dans le sens où les resultats obtenu n'ont pas besoin d'un caractère 'temps réèl' -- des stats --

Merci pour cette idée ;


PS:: je mettrai le resolu quand j'aurai fini la création du package

[Hugues_78]

Un package est un regroupement de procédure / fonctions / variables.

La spécificité du package, c'est que le variables déclarées dedans on une durée de vie qui est égale à celle de la session (connexion) Oracle.

Un petit bémol si le parallélisme est activé : chaque process paralléle correspond à une session propre... Donc je ne suis pas sur de la portée des variables (quelqu'un a déjà fait des tests) ?

Si tu n'as pas besoin d'un grand niveau de temps réel, tu peux te faciliter la vie en créant une table temporaire ou mieux une vue matérialisée.

L'avantage de la vue matérialisée : c'est qu'il n'y a pas de codage pour les purges et autre à faire.

Donc tu ferait :
create materialized view toto compress nologging pctfree0 pctused 99
as
select ... toutes les colonnes

et tu fera ton interrogation sur la vue.

Ou si tu préfére la table :
drop table toto
create table toto compress nologging pctfree 0 pctused 99
as
select ...

et après tu fait tes select from (select union select) group by sur l'objet toto

les paramétres que j'utilises servent :
compress : une compression des blocs de la table (algo LZW, principe du winzip) => gain 2 à 3 espéré pour des données classiques. Le gain se retrouve sur la taille de la table (ou vue matérialisée), donc gain lorsque tu fait un parcours complet de la table (full table scan) : tu lit moins de blocs.
Et encore un gain en mémoire cache : ce sont les blocs qui vont dans le cache, donc dans ce cas des blocs compressés

nologging : c'est un objet provisoire. Inutile de générer des redolog durant la création

pctfree 0 / pctused 99 : on ne garde pas de place libre dans les blocs. Quand on garde un peut d'espace libre, c'est pour faire des updates derrière. Dans ton cas, tu ne va pas en faire.

Si tu choisit l'option table, tu peux faire un create temporary table. Mais la, attention à la purge des données (soit en fin de connexion, soit en fin de transaction)

[jacquesh]

une vue matérialisé (avec auto-refresh) fonctionne très bien.. pas la peine de compliquer

merci pour les idées [je fais qd même joujou avec les packages ]

@++