Utilisation d'un 'record' dans une vue

Invité · 20/03/2008, 09h54

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 !

**mnitu** · 20/03/2008, 10h39

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.

Invité · 25/03/2008, 10h03

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.

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.

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 !

Invité · 25/03/2008, 15h03

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

**orafrance** · 25/03/2008, 15h44

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

**mnitu** · 25/03/2008, 17h21

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

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

**mnitu** · 26/03/2008, 10h43

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.

Invité · 31/03/2008, 13h18

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

Invité · 31/03/2008, 13h19

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 !

**Yorglaa** · 31/03/2008, 13h45

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 :

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

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 :

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

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

Invité · 31/03/2008, 14h18

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

**mnitu** · 31/03/2008, 20h20

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 :

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

**mnitu** · 31/03/2008, 21h25

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 :

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

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 :

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

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

...

C'est complètement faux.

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

**Yorglaa** · 01/04/2008, 09h09

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.

**mnitu** · 01/04/2008, 11h22

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

…
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

…
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 !
…

**Yorglaa** · 01/04/2008, 11h56

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

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

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

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

**mnitu** · 01/04/2008, 17h13

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.