Utilisation d'un 'record' dans une vue

Version imprimable

20/03/2008, 09h54
Invité

Utilisation d'un 'record' dans une vue

Bonjour à tous,

dans notre application, nous gérons différents types de personnes et d'adresses. L'extraction de certains types d'adresses étant compliqué, nous avons une table qui contient ces données. Ces données sont donc censées être gardées à jour en tout temps, à l'aide de triggers et de procédures lancées journalièrement. Bien entendu, c'est assez compliqué et bien entendu aussi, c'est un peu buggé. Et pour couronner le tout, le code date de 10 ans et est franchement indigeste !

Mon travail est donc de réécrire ce code. Mais j'aimerais aller plus loin. J'aimerais ne plus devoir passer par cette table "calculée". Elle est trop compliquée à maintenir à jour. J'ai donc créé des fonctions qui me retournent des record (record d'environ 15 champs). Pour du code PL/SQL, c'est le pied. Par contre, on a beaucoup de vues qui se basent sur cette table que j'aimerais supprimer.

Ma question : y'a-t-il une possibilité, dans une vue, d'utiliser le résultat d'une fonction qui est sous forme de record, sans appeler n fois la fonction ?
J'ai ajouté une fonction pour chaque champs de mon record, mais dans une vue qui a besoin de tous ces champs, la fonction est appelée 15 fois -> c'est lent...

Une idée ???

Merci !
20/03/2008, 10h39
mnitu
Le problème n’est pas que ta fonction est appelée 15 fois pour obtenir chaque valeur, mais que les 15 appels sont effectués autant de fois que des enregistrements ramènes.
Que est-ce que tu veux dire par extraction de ces adresses ? Si vraiment on ne peut pas les éviter il faut peut être étudier l’emploi des vue matérialisés.
Il n’est pas possible d’utiliser des ‘record’ dans un vue mais il est possible :
- d’utiliser des fonctions pipelined
- d’utiliser des objets
Mais je suis loin d’être convaincu que t’a vraiment besoin de ça.
25/03/2008, 10h03
Invité
Citation:

Envoyé par mnitu

Que est-ce que tu veux dire par extraction de ces adresses ?

Par extraction, j'entends la recherche de ces données adresses dans mon schéma. Ca touche 4-5 tables, et suivant les cas, il peut y avoir beaucoup de conditions -> pas possible avec une requête.

Citation:

Envoyé par mnitu

Si vraiment on ne peut pas les éviter il faut peut être étudier l’emploi des vue matérialisés.

J'en utilise déjà pour des données statistiques, mais mise à jour journalièrement. Dans ce cas, la mise à jour doit être immédiate. Je vais étudier la chose.
Citation:
Envoyé par mnitu

Il n’est pas possible d’utiliser des ‘record’ dans un vue mais il est possible :

d’utiliser des fonctions pipelined
d’utiliser des objets

Mais je suis loin d’être convaincu que t’a vraiment besoin de ça.
Je vais quand même y jeter un oeil.

Merci !
25/03/2008, 15h03
Invité

Après quelques recherches et essais :

- une vue matérialisée est basée sur une requête -> pas applicable dans mon cas

- une pipelined fonction marche bien, mais dès que je l'utilise dans un select avec un lien sur une autre table, l'exécution de cette dernière prend un temps fou -> pas utilisable

Je crois que je vais être obligé de remettre en place notre ancien méchanisme, qui se base sur des triggers sur les tables en causes et qui utilise le scheduler Oracle (dbms_job) pour chercher et mettre à jour les données dans cette table "dupliquée"...

Dommage...
25/03/2008, 15h44
orafrance

si la fonction retourne un REF CURSOR ça devrait régler le problème non ?
25/03/2008, 17h21
mnitu

Citation:

Envoyé par orafrance

si la fonction retourne un REF CURSOR ça devrait régler le problème non ?

Je ne pense pas, parce qu’il veut mettre tout ça dans une vue.
Par contre pour l’histoire de

Citation:

…
- une pipelined fonction marche bien, mais dès que je l'utilise dans un select avec un lien sur une autre table, l'exécution de cette dernière prend un temps fou -> pas utilisable
…

il sera mieux d’avoir un exemple à la place d’une conclusion non soutenu par un jeux d’essai.
26/03/2008, 10h43
mnitu

Citation:

Envoyé par olof

...
- une pipelined fonction marche bien, mais dès que je l'utilise dans un select avec un lien sur une autre table, l'exécution de cette dernière prend un temps fou -> pas utilisable
...

Voila un exemple qui démontre plutôt le contraire.
31/03/2008, 13h18
Invité

Citation:

Envoyé par mnitu

il sera mieux d’avoir un exemple à la place d’une conclusion non soutenu par un jeux d’essai.

Un jeu d'essai avec suffisamment de données pour arriver aux temps de réponse que j'ai, ça va être dur... Ce qu'il faut savoir, c'est que cette vue retournerait environ 80000 enregistrements. Un filtrage supplémentaire se fait bien entendu lorsqu'elle est utilisée dans notre application (Forms).
31/03/2008, 13h19
Invité

Citation:

Envoyé par mnitu

Voila un exemple qui démontre plutôt le contraire.

Cet exemple est un peu léger avec sa table de 100 records. La même table avec 100000 records et il faut 95 s. pour récupérer la premier résultat !
31/03/2008, 13h45
Yorglaa
Pour la fonction Pipelined, ça dépend encore de comment tu l'as conçue et utilisée...

si tu l'utilises comme une table standard et que tu la met en jointure dans la clause where, Oracle va attendre d'avoir toutes les lignes pour vérifier la jointure et commencer à te renvoyer les lignes...
Code:

1 2 3 4 5 6 Select ... From tablePrincipale A , Table(maFonctionPipelined) B Where A.maColonne = B.maColonne ...
par contre si tu la code de manière à prendre la clause de jointure en paramètre, elle va chaque fois te renvoyer le résultat de la ligne en cours et du coup faire de "l'asynchrone"... et là c'est tout bénefice !
Code:

1 2 3 4 5 Select ... From tablePrincipale A , Table(maFonctionPipelined(A.maColonne)) B Where ...
ce n'est pas valable dans tous les cas, mais comme tu nous le présente ça peut le faire...
et bien sûr il faut aussi optimiser le select de la fonction pipelined...
31/03/2008, 14h18
Invité

Tout à fait d'accord avec toi Yorglaa. J'ai fait les deux essais. Celui où je passe la jointure en paramètre en bien plus rapide. Mais je crains que ma fonction ne soit trop gourmande et que dès qu'on la sollicite un peu trop, ça pêche...

Je vais encore me pencher sur ma fonction et voir si je peux l'améliorer.

Merci

Citation:

Envoyé par olof

Cet exemple est un peu léger avec sa table de 100 records. La même table avec 100000 records et il faut 95 s. pour récupérer la premier résultat !

Tant que tu ne montre pas par un jeux d'essai, je ne peut pas savoir ce que tu fait.
J'ai repris l'exemple proposé et j'ai le fait passer à 100000 records

Code:

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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
drop table t1
/ 

drop sequence s1
/

drop type jpl_table_type
/

drop type jpl_scalar_type
/

create sequence s1
/

create table t1 as
select
      level                              id,
      mod(level-1,10)                    group_1,
      lpad(mod(level-1,10),10,'0')       group_2,
      trunc((level-1)/10)                data1,
      rpad('x',100)                      padding
from
      dual
connect by level <= 100000
/

create type jpl_scalar_type as object(
      group_1     number,
      group_2     varchar2(10),
      seq_id      number
);
/

create type jpl_table_type as table of jpl_scalar_type;
/


create or replace function pipe_fun
return jpl_table_type
pipelined
as
begin
      for r in (
            select group_1, group_2, s1.nextval seq_id
            from (
                  select distinct group_1, group_2
                  from t1
                  order by group_1, group_2
            )
      )
      loop
            pipe row (jpl_scalar_type( r.group_1, r.group_2, r.seq_id));
      end loop;
      return;
end;
/

break on seq_id skip 1
column padding noprint
set timi on

Select * from 
(select
      /*+ ordered use_hash (t) */
      v.seq_id, 
      t.group_1, 
      t.group_2, 
      t.id, 
      t.data1,
      t.padding
from  
      table(pipe_fun)   v,
      t1          t
where
      t.group_1 = v.group_1
and   t.group_2 = v.group_2
and   rownum < 10
order by
      v.seq_id, t.group_1, t.group_2, t.id
)
where rownum <= 10
/
    SEQ_ID    GROUP_1 GROUP_2            ID      DATA1
---------- ---------- ---------- ---------- ----------
         1          0 0000000000          1          0

         2          1 0000000001          2          0

         3          2 0000000002          3          0

         4          3 0000000003          4          0

         5          4 0000000004          5          0

         6          5 0000000005          6          0

    SEQ_ID    GROUP_1 GROUP_2            ID      DATA1
---------- ---------- ---------- ---------- ----------

         7          6 0000000006          7          0

         8          7 0000000007          8          0

         9          8 0000000008          9          0


9 ligne(s) sÚlectionnÚe(s).

EcoulÚ : 00 :00 :00.92

C'est très, très loin de tes 95 sécondes :)

Citation:

Envoyé par Yorglaa

...

si tu l'utilises comme une table standard et que tu la met en jointure dans la clause where, Oracle va attendre d'avoir toutes les lignes pour vérifier la jointure et commencer à te renvoyer les lignes...

Code:

1
2
3
4
5
6
Select
		...
From	tablePrincipale	A
	, Table(maFonctionPipelined) B
Where	A.maColonne = B.maColonne
...

par contre si tu la code de manière à prendre la clause de jointure en paramètre, elle va chaque fois te renvoyer le résultat de la ligne en cours et du coup faire de "l'asynchrone"... et là c'est tout bénefice !

Code:

1
2
3
4
5
Select
		...
From	tablePrincipale	A
	, Table(maFonctionPipelined(A.maColonne)) B
Where	...

...

C'est complètement faux.

Code:

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
80
 
drop type dept_table_type
/
drop type dept_scalar_type
/
 
create or replace type dept_scalar_type as object (
DEPTNO  NUMBER(2),
DNAME   VARCHAR2(14),
LOC     VARCHAR2(13)
);
/
 
create type dept_table_type as table of dept_scalar_type;
/
 
create or replace function dept_p 
return dept_table_type pipelined
Is
Begin
  For crs In (select * from scott.dept)
  Loop
    pipe row(dept_scalar_type(crs.deptno, crs.dname, crs.loc));
  End Loop;
  return;
End;
/
 
create or replace function dept_p2(p_deptno In Number) 
return dept_table_type pipelined
Is
Begin
  For crs In (select * from scott.dept Where deptno = p_deptno)
  Loop
    pipe row(dept_scalar_type(crs.deptno, crs.dname, crs.loc));
  End Loop;
  return;
End;
 
SQL> select *
  2  from scott.emp e, Table(dept_p) d
  3  Where e.deptno = d.deptno
  4  /
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2504524478
 
---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        | 38117 |  1451K|    29   (7)| 00:00:01 |
|*  1 |  HASH JOIN                         |        | 38117 |  1451K|    29   (7)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | EMP    |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| DEPT_P |       |       |            |          |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"=VALUE(KOKBF$))
 
SQL>
SQL>
SQL> select *
  2  from scott.emp e, Table(dept_p2(e.deptno)) d
  3  /
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2633834785
 
----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |   114K|  4355K|   317   (2)| 00:00:04 |
|   1 |  NESTED LOOPS                      |         |   114K|  4355K|   317   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL                | EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| DEPT_P2 |       |       |            |          |
----------------------------------------------------------------------------------------------

Ce qui se passe en fait c'est que le plan d'exécution change due à la sous- interrogation corrélé. Dans le deuxième cas il est obligé d'exécuter en utilisant le NESTED LOOP et cela n'est pas forcement bénéfique, plutôt le contraire est vrai si le volume des données est important.

01/04/2008, 09h09
Yorglaa

tout dépend où et comment est utilisée la fonction pipelined...

on ne peut jamais dire que le hash ou le nested loop sera plus ou moins rapide que l'autre comme ça... ça dépends non seulement de la volumétrie globale des tables, mais aussi de la volumétrie de CHAQUE table, des indexes, des stats, etc...

dans le cas où tu as une petite table et une grosse, un NL avec la petite table en inner table sera bien plus performant qu'un hash, par contre il faut bien placer les tables les jointures, etc...
et encore il faut aussi prendre en compte les paramètres de la DB, les performances du serveur, la charge machine, etc.

il est clair que le Select de la fonction pipelined devra être optimisé en fonction de son utilisation, c'est à dire pour être utilisé avec ou sans paramètres...

Mais toujours est-il que la clef de l'optimisation est de ne jamais faire de jugements à l'emporte pièce sur la base de quelque chose d'aussi trivial qu'un "Select * from Emp", mais de toujours se baser sur une cas de la vie réelle...

Par exemple, si une fonction pipelined sans paramètre ramène 1'000'000 de lignes, MAIS que le reste du Select principal (qui va être joint avec la fonction) ramène 2'000 lignes, es-tu bien certain qu'il vaut mieux utiliser une fonction sans paramètre ??
Pour l'avoir testé dans la vie réelle je t'assure que non.
01/04/2008, 11h22
mnitu

Voilà que maintenant on pourrait commencer à se mettre d’accord : les performances dépendent des plusieurs facteurs et il est incorrecte de dire que mettre la jointure dans la fonction pipelined va améliorer toujours les performances.
Par contre faire des jugements de valeurs sur la base des jeux des tests reproductibles est de loin supérieur à des jugements mythologiques de type

Citation:

…
une pipelined fonction marche bien, mais dès que je l'utilise dans un select avec un lien sur une autre table, l'exécution de cette dernière prend un temps fou -> pas utilisable
…

ou

Citation:

…
par contre si tu la code de manière à prendre la clause de jointure en paramètre, elle va chaque fois te renvoyer le résultat de la ligne en cours et du coup faire de "l'asynchrone"... et là c'est tout bénefice !
…
01/04/2008, 11h56
Yorglaa

bouarf...
à mon avis il encore plus mythologique de s'appuyer sur des exemple pseudo concrets avec les tables exemple d'Oracle (EMP, SAL, DEPT, etc...).

ces tables sont très bien pour un "proof of concept" technologique mais n'apportent strictement rien par rapport à la réalité en termes de perf, puisque chaque réalité et différente et toujours bien plus complexe que le schéma exemple d'Oracle.

ceci dit, et pour répondre plus directement à ton Post précédent, je ne me prononcerais pas sur la première citation puisqu'elle n'est pas de moi...
quand à la seconde il est clair que cette citation est à mettre dans le contexte de la discussion courante, et dans un but de rechercher une solution spécifique au problème de olof.

il n'est pas dans mon propos d'en faire un propos général... puisque justement en terme d'optimisation il n'existe aucune généralité...

d'ailleurs du même accabit on pourrait aussi citer

Citation:

Ce qui se passe en fait c'est que le plan d'exécution change due à la sous- interrogation corrélé. Dans le deuxième cas il est obligé d'exécuter en utilisant le NESTED LOOP et cela n'est pas forcement bénéfique, plutôt le contraire est vrai si le volume des données est important.

puisque cette affirmation ne se base que sur un cas précis et ciblé (toujours le schéma exemple Oracle) et ne prouve strictement rien dans le contexte opérationnel de olof.

de même que je t'invite à réfléchir sur ta déclaration pour le moins péremptoire

Citation:

C'est complètement faux.

puisque là encore cette déclaration n'est valable que dans le cas précis que tu présente...
et en ce qui concerne cette affirmation, je t'invite à réfléchir sur ce point (je me permets de m'auto-citer) :

Citation:

si une fonction pipelined sans paramètre ramène 1'000'000 de lignes, MAIS que le reste du Select principal (qui va être joint avec la fonction) ramène 2'000 lignes, es-tu bien certain qu'il vaut mieux utiliser une fonction sans paramètre ??

et pour finir, je suis bien entendu d'accord avec toi qu'un cas reproductible est bien plus adapté à des tests que la théorie, mais comme justement tout ce qui concerne l'optimisation n'est que très difficilement reproductible (volumétrie équivalente, machine et charge équivalente, etc) je pense qu'il est plus profitable de soulever des pistes que notre ami pourra tester dans son propre environnement que de prétendre avoir la vérité ultime avec les tables EMP et consort...
01/04/2008, 17h13
mnitu

Citation:

Envoyé par Yorglaa

...
il n'est pas dans mon propos d'en faire un propos général... puisque justement en terme d'optimisation il n'existe aucune généralité...

d'ailleurs du même accabit on pourrait aussi citer

Citation:
Ce qui se passe en fait c'est que le plan d'exécution change due à la sous- interrogation corrélé. Dans le deuxième cas il est obligé d'exécuter en utilisant le NESTED LOOP et cela n'est pas forcement bénéfique, plutôt le contraire est vrai si le volume des données est important.

puisque cette affirmation ne se base que sur un cas précis et ciblé (toujours le schéma exemple Oracle) et ne prouve strictement rien dans le contexte opérationnel de olof.
...

Peut tu fournir un contre-exemple avec les tables que tu veut ? Juste un contre-exemple.
01/04/2008, 17h19
orafrance

restons calme les amis ;)
01/04/2008, 17h33
Yorglaa

mais nous sommes très calme... je suis sûr que mnitu réagi comme moi, c'est à dire que "le débat fait avancer la connaissance"...

exemple suit...
01/04/2008, 17h36
mnitu

Citation:

Envoyé par Yorglaa

mais nous sommes très calme... je suis sûr que mnitu réagi comme moi, c'est à dire que "le débat fait avancer la connaissance"...

exemple suit...

Oui, c'est vrai. Je suis d'accord avec une partie de tes affirmations mais comme je l'ai dit je suis bien contre une autre partie..

OK, alors voici un exemple tiré de notre DB de Prod.
Tu comprendras bien sûr que je ne peux pas donner la structure et les données ici...

Contexte des tables :

Citation:

Requester, équivalent à une table client
AdministrativeProcedure, le nom est évident, directement lié à Requester avec une FK

on va travailler sur un échantillon :

Citation:

GME@rasp> select count(*) from requester where Requester_i between 40000 and 45000 ;

COUNT(*)
----------
2454

Ecoulé : 00 :00 :00.01
/************************************/

GME@rasp> Select count(*) from AdministrativeProcedure where Requester_i between 40000 and 45000 ;

COUNT(*)
----------
44861

Ecoulé : 00 :00 :00.04

Une fonction pipelined qui ramènes le dernier élément d'un type donné de procédure administrative, par "Requester"
Une fois sans paramètre (jointure "après") et une fois avec un paramètre (jointure "avant").

sans paramètre :

Code:

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
Function FGetLastTransferTab Return AdminProcTable Pipelined
Is
 
    Cursor cTrsf is
        Select  distinct
                A.Requester_i
                , A.T_ProcedureEvent_i
                , substr(Utility.FGetItemCode(A.T_ProcedureEvent_i),1,35) as Code
                , A.EventDate
                , A.Delay
        From
                AdministrativeProcedure A
        Where
                A.T_ProcedureEventClass_i = 581
        And     A.EventDate = (
                                Select  max(A2.EventDate)
                                From    AdministrativeProcedure A2
                                Where   A.Requester_i = A2.Requester_i
                                And     A2.T_ProcedureEventClass_i = 581
                              ) ;
 
Begin
        For i in cTrsf
        Loop
            pipe row ( AdminProcType (
                                        i.requester_i
                                        , i.T_ProcedureEvent_i
                                        , i.Code
                                        , i.EventDate
                                        , i.Delay
                                     )
                     ) ;
        End Loop ;
 
        Return ;
 
End FGetLastTransferTab ;

et avec :

Code:

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
Function FGetLastTransferTab ( pn_Requester_i IN  Number
                             )
        Return AdminProcTable Pipelined
Is
 
    Cursor cTrsf is
        Select  distinct
                A.Requester_i
                , A.T_ProcedureEvent_i
                , substr(Utility.FGetItemCode(A.T_ProcedureEvent_i),1,35) as Code
                , A.EventDate
                , A.Delay
        From
                AdministrativeProcedure A
        Where
                A.T_ProcedureEventClass_i = 581
        And     A.EventDate = (
                                Select  max(A2.EventDate)
                                From    AdministrativeProcedure A2
                                Where   A.Requester_i = A2.Requester_i
                                And     A2.T_ProcedureEventClass_i = 581
                              )
        And     A.Requester_i = pn_Requester_i ;
 
Begin
        For i in cTrsf
        Loop
            pipe row ( AdminProcType (
                                        i.requester_i
                                        , i.T_ProcedureEvent_i
                                        , i.Code
                                        , i.EventDate
                                        , i.Delay
                                     )
                     ) ;
        End Loop ;
 
        Return ;
 
End FGetLastTransferTab ;

exécution sur SQLPlus avec Set AutoTrace on, résultat toujours à la 2ème exécution, pour que les blocs soient bien au chaud dans le buffer ...

sans paramètre :

Code:

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
Select
        R.Requester_i
        , T.*
From    Requester   R
        , Table(Utility.FGetLastTransferTab) T
Where 
        R.Requester_i between 40000 and 45000 
And     R.Requester_i = T.Requester_i ;
 
-- ici les données
 
2454 ligne(s) sélectionnée(s).
 
Ecoulé : 00 :00 :18.44
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=2 Bytes=14)
   1    0   NESTED LOOPS (Cost=11 Card=2 Bytes=14)
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'FGETLASTTRANSFER
          TAB'
 
   3    1     INDEX (UNIQUE SCAN) OF 'REQUESTER_PK1' (UNIQUE)
 
 
 
 
Statistiques
----------------------------------------------------------
      61792  recursive calls
         20  db block gets
     102745  consistent gets
       6540  physical reads
          0  redo size
     109136  bytes sent via SQL*Net to client
       1420  bytes received via SQL*Net from client
        330  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
       2454  rows processed

et avec paramètre :

Code:

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
Select
        R.Requester_i
        , T.*
From    Requester   R
        , Table(Utility.FGetLastTransferTab(R.Requester_i)) T
Where 
        R.Requester_i between 40000 and 45000 ;
 
-- ici les données
 
2454 ligne(s) sélectionnée(s).
 
Ecoulé : 00 :00 :06.46
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=25823 Card=19168334
          Bytes=134178338)
 
   1    0   NESTED LOOPS (Cost=25823 Card=19168334 Bytes=134178338)
   2    1     INDEX (RANGE SCAN) OF 'REQUESTER_PK1' (UNIQUE) (Cost=6 C
          ard=2347 Bytes=11735)
 
   3    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'FGETLASTTRANSFER
          TAB'
 
 
 
 
 
Statistiques
----------------------------------------------------------
       9816  recursive calls
          0  db block gets
      31307  consistent gets
          0  physical reads
          0  redo size
     109136  bytes sent via SQL*Net to client
       1420  bytes received via SQL*Net from client
        330  SQL*Net roundtrips to/from client
       7362  sorts (memory)
          0  sorts (disk)
       2454  rows processed

résultat = 18 secondes sans paramètre et 6 secondes avec le paramètre...

02/04/2008, 10h19
Invité

Bonjour à tous,

je vois que ma question soulève un débat intéressant, très bien !

mnitu, dans le test que je mentionnais plus, j'ai non seulement peuplé la table t1 avec 100'000 records, mais j'ai aussi modifié la fonction pipelined, pour un peu plus coller à mon cas, où la fonction est légèrement complexe. D'où mon temps de réponse de 95 s.

Sinon, j'ai refait des tests avec la méthode proposée par Yorglaa, à savoir faire en sorte que la pipelined fonction reçoit en paramètre la clé qui permet de faire la jointure, et, dans mon cas, ça devient intéressant. Il faut que je creuse encore un peu, car cette fameuse table que j'aimerais remplacée est utilisée à beaucoup d'endroits !

En tout cas, merci à vous pour vous éclaircissements !
02/04/2008, 16h26
mnitu

Citation:

Envoyé par olof

...
mnitu, dans le test que je mentionnais plus, j'ai non seulement peuplé la table t1 avec 100'000 records, mais j'ai aussi modifié la fonction pipelined, pour un peu plus coller à mon cas, où la fonction est légèrement complexe. D'où mon temps de réponse de 95 s.
...

D’accord mais tu aurait pu posté ton code à la place d’une affirmation … Ok, ce n’est pas grave.

Pour Yorglaa,
j’attendais un exemple qui passe la « jointure » dans fonction pipelined et qui n’utilise pas NESTED LOOP dans le plan d’exécution. Mon affirmation était que c’est le changement du plan qui implique les changements des temps de réponse. Et que dans ce cas, utilisation d’une sous-interrogation corrélée, le plan d’exécution utiliserait toujours un NESTED LOOP, et que cella n'est pas toujours bénéfique.
02/04/2008, 18h04
Yorglaa

je ne te comprends pas...

en passant la jointure en paramètre, le resultset de retour ne sera FORCEMENT que les lignes concernée => peu de ligne => nested loop

Logique...

le hash join étant conçu plutôt pour traiter un nb de lignes plus grand que le NL, il est logique que le hash va prendre plus de temps en relation avec un resultset petit, tout comme il est logique que le NL va être plus performant sur un petit resultset...

je ne vois pas où tu veux en venir, je suis désolé...

Pour revenir à mon exemple, il est clair que si je veux traiter l'ensemble de ma table requester, j'aurais plutôt tendance à utiliser la fonction sans paramètres... puisque je veux l'ensemble des 2 tables.

Encore que là, il plus question d'une cardinalité (puisque ma fonction ne va prendre qu'un seul type de donnée) que de masse "pure" de lignes...

Re...
pour être complet, j'ai essayé le même test, sans paramètre mais en forçant le hash join avec un hint.

Code:

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
SELECT /*+ USE_HASH (R) */
        R.Requester_i
        , T.*
FROM    Requester   R
        , TABLE(Utility.FGetLastTransferTab) T
Where   R.Requester_i = T.Requester_i 
and     R.Requester_i BETWEEN 40000 AND 45000 ;
 
-- les données
 
2454 ligne(s) sélectionnée(s).
 
Ecoulé : 00 :00 :17.90
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=2 Bytes=14)
   1    0   HASH JOIN (Cost=18 Card=2 Bytes=14)
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'FGETLASTTRANSFER
          TAB'
 
   3    1     INDEX (RANGE SCAN) OF 'REQUESTER_PK1' (UNIQUE) (Cost=6 C
          ard=2347 Bytes=11735)
 
 
 
 
 
Statistiques
----------------------------------------------------------
      61802  recursive calls
         20  db block gets
     100324  consistent gets
       4252  physical reads
          0  redo size
     107989  bytes sent via SQL*Net to client
       1420  bytes received via SQL*Net from client
        330  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
       2454  rows processed

pas de grande modification...

mais par contre la même chose AVEC le paramètre revient invariablement au Nested Loop... il semble qu'Oracle ne veuille pas accepter le hash join, même avec le hint... étrange non ?

03/04/2008, 17h32
mnitu

Peut tu me donner la version de la base Oracle ou t'a fait tes tests ?
03/04/2008, 17h34
mnitu

Citation:

Envoyé par Yorglaa

...

mais par contre la même chose AVEC le paramètre revient invariablement au Nested Loop... il semble qu'Oracle ne veuille pas accepter le hash join, même avec le hint... étrange non ?

Voila ce que j'essaie de te dire :)
03/04/2008, 18h29
Yorglaa

aaaahhh ben finalement on est d'accord alors !! :yaisse2: :king:

je suis en 9.2.0.7, serveur Sun 64 bits