Utiliser une fonction stockée

**sdanvbnet** · 24/05/2008, 19h13

Bonjour,

Questions :
1. Quelle est le nombre d'enregistrements de chacune des 3 tables ?
2. Pourquoi ne pas fusionner les tables ANNONCE et ANNONCE_IMAGE
pour simplifier la jointure ?
3. Peux-tu nous donner la structure de tes 3 tables ? et le code de
création des 3 tables ?

Proposition
Il faut remplacer SUM(SAC.annonce_sms + SAC.annonce_mail + SAC.mailagence_r) par une fonction si possible.
-----------------------------------------------------------------------

SELECT AN2.id_annonce,
COUNT(id_image) AS nb_photo,
SUM(SAC.resultat_r) AS resultat_r,
SUM(SAC.annonce_d) AS annonce_d,
SUM(SAC.annonce_sms + SAC.annonce_mail + SAC.mailagence_r) AS other_stats
FROM ANNONCE AN2,
ANNONCE_IMAGE AI,
STATISTIQUE_ANNONCE_COMBINE SAC
WHERE AN2.id_annonce = AI.id_annonce(+)
AND AN2.id_presto = '566066'
AND AI.id_statut_annonce_image(+) > 0
AND SAC.id_annonce = AN2.id_annonce
AND SAC.date_statistique_annonce BETWEEN to_date('010408','ddmmyy') AND to_date('300408','ddmmyy')
GROUP BY AN2.id_annonce

**mnitu** · 25/05/2008, 11h06

Envoyé par sdanvbnet

...
2. Pourquoi ne pas fusionner les tables ANNONCE et ANNONCE_IMAGE
pour simplifier la jointure ?

Envoyé par sdanvbnet

...

Proposition
Il faut remplacer SUM(SAC.annonce_sms + SAC.annonce_mail + SAC.mailagence_r) par une fonction si possible.
...

Peut tu expliquer à quoi sa servira ?

**sdanvbnet** · 25/05/2008, 20h00

Envoyé par mnitu

Peut tu expliquer à quoi sa servira ?

Merci de répondre aux questions 1 et 3 du post précédent. Ca permettra de t'aider.

Avec la structure des tables, on peut avoir des idées et des propositions
plus précises.

Pour info, une procédure stockée est plus rapide en exécution.

**orafrance** · 26/05/2008, 10h28

Envoyé par sdanvbnet

Pour info, une procédure stockée est plus rapide en exécution.

et tu peux nous expliquer comment un appel de PL/SQL pourrait être plus rapide que du SQL natif ?

La preuve que tu as tord sur un exemple simple :

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
SQL> create table my_table as select * from dba_segments;
 
Table created.
 
SQL> alter table my_table storage (buffer_pool recycle);
 
Table altered.
 
SQL> begin 
  2  for i in 1..1000 loop
  3  insert into my_table ( select * from dba_segments);
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> create or replace function ma_func(a number, b number, c number)
  2  return number as
  3  begin
  4  return a + b + c;
  5  end;
  6  /
 
Function created.

Code :

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

1
2
3
4
5
6
7
set autotrace traceonly
set timing on
SQL> select sum(HEADER_FILE+HEADER_BLOCK+bytes), OWNER  , SEGMENT_TYPE
  2  from my_table
  3  group by OWNER  , SEGMENT_TYPE;
 
33 rows selected.

Elapsed: 00:00:06.70

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'MY_TABLE'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
46670 consistent gets
38040 physical reads
0 redo size
1517 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
33 rows processed

Code :

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

1
2
3
4
5
6
 
SQL> select sum(ma_func(HEADER_FILE,HEADER_BLOCK,bytes)), OWNER  , SEGMENT_TYPE
  2  from my_table
  3  group by OWNER  , SEGMENT_TYPE;
 
33 rows selected.

Elapsed: 00:00:13.58

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'MY_TABLE'

Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
46681 consistent gets
38040 physical reads
0 redo size
1526 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
33 rows processed

presque 2 fois plus long avec une fonction

Et évidemment les recursive calls y sont pour quelque chose

**mnitu** · 26/05/2008, 11h00

Envoyé par sdanvbnet

...
Pour info, une procédure stockée est plus rapide en exécution.

J’attends avec intérêt la démonstration

**miloux32** · 26/05/2008, 17h00

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT AN2.id_annonce,
           COUNT(id_image) AS nb_photo,
           SUM(SAC.resultat_r) AS resultat_r,
           SUM(SAC.annonce_d) AS annonce_d,
           SUM(SAC.annonce_sms + SAC.annonce_mail + SAC.mailagence_r) AS other_stats
      FROM ANNONCE AN2, 
           ANNONCE_IMAGE AI, 
           STATISTIQUE_ANNONCE_COMBINE SAC
     WHERE AN2.id_annonce = AI.id_annonce(+)
       AND AN2.id_presto = '566066'
       AND AI.id_statut_annonce_image(+) > 0
       AND SAC.id_annonce = AN2.id_annonce
       AND SAC.date_statistique_annonce BETWEEN to_date('010408','ddmmyy') AND to_date('300408','ddmmyy')
     GROUP BY AN2.id_annonce

Je suis d'accord avec Orafrance, il n'est pas normal que le temps soit important pour cette requete. Vérifie les stats.

J'ai toutefois une question ...
a quoi sert ton

Code :

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

AND AI.id_statut_annonce_image(+) > 0

C'est surtout la jointure externe qui me trouble ....
un nvl ou "Or AI.id...image is null" ne feraient ils pas l'affaire si c'est un problème de jointure à cause de l'autre jointure externe...

Si jamais les perfs étaient toujours catastrophiques, tu pourrais toujours gagner un peu de temps en créeant une sous-requete avec tes conditions sur SAC et AN2 (pas de jointure externe etc ) et en joignant ce resultat à l'autre table.
Mais bon je pense que le gain est minime.

je te conseille donc de faire déjà :

Code :

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

analyze table NOMDELATABLE compute statistics;

Pour mettre à jour les stats
etde faire aussi un

Code :

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

ANALYZE INDEX nomdelindex;

pour chacun des index de tes tables.

(oui je sors du débat "Procedure stockée " plus rapide que tout parce que ca n'aide pas le monsieur et que c'est un non-sens franchement de penser ça meme une seconde)

**orafrance** · 26/05/2008, 17h09

Envoyé par miloux32

je te conseille donc de faire déjà :

Code :

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

analyze table NOMDELATABLE compute statistics;

Pour mettre à jour les stats

DBMS_STATS, pas ANALYZE

Enfin, sauf s'il est en 8 ou avant

**miloux32** · 26/05/2008, 17h24

Envoyé par orafrance

DBMS_STATS, pas ANALYZE

Enfin, sauf s'il est en 8 ou avant

Oui tu as raison (mais bon comme la version n'est pas indiquée je me suis permis de donner l'autre solution ! )
(et pas la peine d'utiliser ton smiley flingueur ! )

Invité · 26/05/2008, 17h27

Envoyé par miloux32

Oui tu as raison (mais bon comme la version n'est pas indiquée je me suis permis de donner l'autre solution ! )
(et pas la peine d'utiliser ton smiley flingueur ! )

Pas de mauvaise foi !
C'est une bd 10g, cf plus haut !

**orafrance** · 26/05/2008, 17h39

Envoyé par miloux32

(et pas la peine d'utiliser ton smiley flingueur ! )

je l'aime trop

**sdanvbnet** · 27/05/2008, 13h01

Envoyé par mnitu

Peut tu expliquer à quoi sa servira ?

Nous sommes sur un forum. Merci d'écrire des messages compréhensibles.

Pour info, une jointure sur (N-X) tables avec X>0 est plus plus qu'une jointre sur N tables. Ca se démontre mathématiquement.

**mnitu** · 27/05/2008, 13h25

Envoyé par sdanvbnet

Nous sommes sur un forum. Merci d'écrire des messages compréhensibles.

Pour info, une jointure sur (N-X) tables avec X>0 est plus plus qu'une jointre sur N tables. Ca se démontre mathématiquement.

Ne te fâche pas !
1) Ta proposition de fusionner les tables pour simplifier la jointure est fondamentalement erronée. Le model relationnel n’a pas comme but de simplifier les jointures ! Les bases des données existent pour faire des jointures ; elle sont très bon pour ça!
3) Il n’est jamais plus performant de remplacer le SQL par le PL/SQL.

Tom Kyte Mantra
My mantra, that I'll be sticking with thank you very much, is:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL.
• If you cannot do it in PL/SQL, try a Java Stored Procedure.
• If you cannot do it in Java, do it in a C external procedure.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

think in sets...

learn all there is to learn about SQL...

**orafrance** · 27/05/2008, 14h36

Envoyé par sdanvbnet

Nous sommes sur un forum. Merci d'écrire des messages compréhensibles.

Qu'est ce que tu n'as pas compris ?

Envoyé par sdanvbnet

Pour info, une jointure sur (N-X) tables avec X>0 est plus plus qu'une jointre sur N tables. Ca se démontre mathématiquement.

Ce qui est vrai en mathématique ne l'est pas forcément en modélisation. Tu sembles oublier les FK et indexes qui permettent d'éviter que la multiplication de table dans une requête n'influe sur les performances. Codd et Merise n'ont pas travaillé sur la normalisation juste histoire de perdre du temps.

Dans son cas, si une annonce à plusieurs images et que la table ANNONCE a 30 colonnes, tu répétes ces 30 colonnes autant de fois qu'il y a d'image ?