Utilisation d'un 'record' dans une vue

**Yorglaa** · 01/04/2008, 17h45

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 :

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

on va travailler sur un échantillon :

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 :

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
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 :

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
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 :

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
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 :

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
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...

**olof** · 02/04/2008, 10h19

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 !

**mnitu** · 02/04/2008, 16h26

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.

**Yorglaa** · 02/04/2008, 18h04

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...

**Yorglaa** · 02/04/2008, 18h16

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 :

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
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 ?

**mnitu** · 03/04/2008, 17h32

Peut tu me donner la version de la base Oracle ou t'a fait tes tests ?

**mnitu** · 03/04/2008, 17h34

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

**Yorglaa** · 03/04/2008, 18h29

aaaahhh ben finalement on est d'accord alors !!

je suis en 9.2.0.7, serveur Sun 64 bits

Utilisation d'un 'record' dans une vue

SQL Oracle

Discussions similaires

Partager

Partager