Discussion :

[Rams7s]

Bonjour,

J'ai plus ou moins cette situation:
Mon programme parcourt plein plein plein de fois la même table pour récupérer une valeur sur laquelle il y a un index.
Et il connait avant de faire le select, l'ensemble des valeurs qu'il va devoir récupérer.

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
drop table tmp2;
drop table tmp2_temporary;
 
 
CREATE TABLE tmp2 AS (SELECT level AS n, lpad(level,8,'x') AS data FROM dual connect BY level<=100000);
CREATE INDEX tmp2_ind ON tmp2(n,data);
 
PROMPT ============
PROMPT test1
declare
   l_s tmp2.data%type;
begin
   for i in 1..100000 loop
      select data into l_s from tmp2 where n=trunc(dbms_random.value(1,1000));
   end loop;
end;
/

En utilisant un tableau, on gagne du temps, c'est toujours ca de pris mais ce n'est toujours pas fantastique.

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
PROMPT ============
PROMPT test2
declare
 type  t_num is table of number;
   a t_num:=t_num();
   l_s tmp2.data%type;
begin
   for i in 1..100000 loop
      a.extend;
      a(i):=trunc(dbms_random.value(1,1000));
   end loop;
 
   for i in a.first..a.last loop
      select data into l_s from tmp2 where n=a(i);
   end loop;
end;
/

Mais ce n'est pas hyper rapide. Enfin, disons qu'en utilisant une table temporaire qui va contenir toutes mes valeurs random je gagne beaucoup de temps:

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
 
PROMPT ============
PROMPT test3
create global temporary table tmp2_temporary (n2 number);
declare
   type t_num is table of number;
   a t_num:=t_num();
   type t_data is table of tmp2.data%type;
   l_s t_data;
begin
   for i in 1..100000 loop
      a.extend;
      a(i):=trunc(dbms_random.value(1,1000));
   end loop;
   forall i in a.first..a.last insert into tmp2_temporary values (a(i));
   select data bulk collect into  l_s from tmp2 inner join tmp2_temporary on n=n2;
   dbms_output.put_line(l_s.count);
end;
/

test1
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.13

test2
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.09

test3
Table created.
Elapsed: 00:00:00.00
100000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.32

Par contre, je n'ai pas très envie d'utiliser une table temporaire. Psychologiquement ca me bloque.
Mais comme c'est quand même un ordre de grandeur plus rapide que mes autres solutions...

Auriez-vous une idée pour faire la même chose sans la table temporaire ?
Parce que que concrètement, c'est plus rapide avec la table simplement grâce au bulk collect, mais je ne trouve pas d'idée pour utiliser le bulk collect sans la table temporaire.

Genre un equivalent de forall mais avec un select?

[pacmann]

Salut !

Et tu ne peux pas plutôt mettre ton tmp2 sous forme de tableau ?

Et surtout ce truc là, c'est juste un exemple bidon ?

Code :

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

1
2
 
CREATE TABLE tmp2 AS (SELECT level AS n, lpad(level,8,'x') AS DATA FROM dual connect BY level<=100000)

Parce que sinon, tu peux te passer de la lecture et directement faire :

Code :

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

1
2
 
l_s := lpad(trunc(dbms_random.value(1,1000)), 8, 'x')

[skuatamad]

Par contre, je n'ai pas très envie d'utiliser une table temporaire. Psychologiquement ca me bloque.

Je comprends mais bon s'il faut vraiment...l'avantage c'est que c'est indexable.

Sinon il est possible de faire créer une table temporaire à la volée par oracle en lui demendant de materialiser les lignes, ce qui peut être plus performant (Sans hint l'optimiseur est libre de choisir ou non de matérialiser):

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
SQL>   with tmp2_temporary as (
  2  select /*+ materialize */ trunc(dbms_random.value(1,1000)) as n2 from dual connect by level <= 100000
  3  )
  4  SELECT DATA
  5    FROM tmp2
  6    JOIN tmp2_temporary ON n=n2
  7  /
 
100000 rows selected.
 
Elapsed: 00:00:04.39
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2537569970
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |     1 |    27 |     5   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D664D_32B4F4 |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|                           |       |       |            |          |
|   4 |     FAST DUAL                  |                           |     1 |       |     2   (0)| 00:00:01 |
|   5 |   NESTED LOOPS                 |                           |     1 |    27 |     3   (0)| 00:00:01 |
|   6 |    VIEW                        |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D664D_32B4F4 |     1 |    13 |     2   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | TMP2_IND                  |     1 |    14 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(LEVEL<=100000)
   8 - access("N"="N2")
 
 
Statistics
----------------------------------------------------------
          3  recursive calls
        161  db block gets
     120746  consistent gets
        152  physical reads
        600  redo size
    2240500  bytes sent via SQL*Net to client
      73850  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed
 
SQL>

C'est toujours un peu plus lent que la version table temmporaire, mais l'ordre de grandeur me semble acceptable :

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
SQL> declare
  2     type t_data IS TABLE of tmp2.data%type;
  3     l_s t_data;
  4  begin
  5      with tmp2_temporary as (
  6    select /*+ materialize */ trunc(dbms_random.value(1,1000)) as n2 from dual connect by level <= 100000
  7    )
  8    SELECT DATA bulk collect INTO  l_s
  9      FROM tmp2
 10      JOIN tmp2_temporary ON n=n2;
 11    dbms_output.put_line(l_s.count);
 12  end;
 13  /
100000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:01.31
SQL> 
SQL> truncate table tmp2_temporary
  2  /
 
Table truncated.
 
Elapsed: 00:00:00.07
SQL> declare
  2     type t_num IS TABLE of number;
  3     a t_num:=t_num();
  4     type t_data IS TABLE of tmp2.data%type;
  5     l_s t_data;
  6  begin
  7     FOR i IN 1..100000 loop
  8        a.extend;
  9        a(i):=trunc(dbms_random.value(1,1000));
 10     end loop;
 11     forall i IN a.first..a.last INSERT INTO tmp2_temporary VALUES (a(i));
 12     SELECT DATA bulk collect INTO  l_s FROM tmp2 INNER JOIN tmp2_temporary ON n=n2;
 13     dbms_output.put_line(l_s.count);
 14  end;
 15  /
100000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.55
SQL>

Mais, à mon avis, la différence de temps d'exécuction ne provient pas de la création à la voler de SYS_TEMP_0FD9D664D_32B4F4 mais plutôt du changement de contexte entre le moteur SQL et le moteur PL/SQL engendré par l'utilisation du package dbms_random.
D'ailleurs si on utilise des fonctions SQL les perfs sont plus proches :

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
SQL> declare
  2     type t_data IS TABLE of tmp2.data%type;
  3     l_s t_data;
  4  begin
  5      with tmp2_temporary as (
  6    select /*+ materialize */ mod(level,100)+level as n2 from dual connect by level <= 100000
  7    )
  8    SELECT DATA bulk collect INTO  l_s
  9      FROM tmp2
 10      JOIN tmp2_temporary ON n=n2;
 11    dbms_output.put_line(l_s.count);
 12  end;
 13  /
99951
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.42
SQL> 
SQL> truncate table tmp2_temporary
  2  /
 
Table truncated.
 
Elapsed: 00:00:00.09
SQL> declare
  2     type t_num IS TABLE of number;
  3     a t_num:=t_num();
  4     type t_data IS TABLE of tmp2.data%type;
  5     l_s t_data;
  6  begin
  7     FOR i IN 1..100000 loop
  8        a.extend;
  9        a(i):=mod(i,100)+i;
 10     end loop;
 11     forall i IN a.first..a.last INSERT INTO tmp2_temporary VALUES (a(i));
 12     SELECT DATA bulk collect INTO  l_s FROM tmp2 INNER JOIN tmp2_temporary ON n=n2;
 13     dbms_output.put_line(l_s.count);
 14  end;
 15  /
99951
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.35
SQL>

Ce qui montre bien que le moteur PL/SQL est totalement optimisé pour exécuter du SQL (forall, bulk collect...), mais qu'à l'inverse le moteur SQL l'est moins pour exécuter des packages PL/SQL !

Bon la différence n'est pas énorme mais si besoin, il est possible d'améliorer les perfs liées à ce changement de contexte en passant par une fonction pipelined.

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
SQL> create or replace type table_number as table of number
  2  /
 
Type created.
 
Elapsed: 00:00:00.04
SQL> 
SQL> create or replace function pipe_rand
  2  return table_number
  3  pipelined
  4  as
  5  begin
  6    FOR i IN 1..100000 loop
  7        pipe row (trunc(dbms_random.value(1,1000)));
  8     end loop;
  9    return;
 10  end;
 11  /
 
Function created.
 
Elapsed: 00:00:00.03
SQL>

Chez moi le temps d'exécution est même un peu meilleur qu'avec la table temporaire :

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
SQL> declare
  2     type t_data IS TABLE of tmp2.data%type;
  3     l_s t_data;
  4  begin
  5      with tmp2_temporary as (
  6    select column_value as n2 from table(pipe_rand)
  7    )
  8    SELECT DATA bulk collect INTO  l_s
  9      FROM tmp2
 10      JOIN tmp2_temporary ON n=n2;
 11    dbms_output.put_line(l_s.count);
 12  end;
 13  /
100000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.46
SQL>

Bon je ne sais pas si c'est adaptable à ton code réel, il est en tout cas évident qu'il faut essayer de réduire au maximum cette partie :

Mon programme parcourt plein plein plein de fois la même table

PS : C'est annexe, mais les cardinnalités estimées affichées par autotrace (et donc à mon avis aussi le plan utilisé) sont vraiment à l'ouest peut être à cause du from dual où le CBO ne tiendrait pas compte du connect by...je ne sais pas, quelqu'un a-t-il une idée ?
L'utilisation du hint dynamic_sampling pourrait améliorer l'estimation, mais pour l'instant je n'ai pas trouvé comment...

[Rams7s]

Bonjour,

Oui tmp2 est une table de test. Mais c'est gentil d'avoir propose cette solution

Pour le changement de contexte, ce sont les cas de test1 et test2. Et en effet, le test2 est bien plus rapide.

La function pipelined ne va pas m'aider dans le cas present.
En fait, j'ai une liste de valeur en java, et je veux recuperer toutes les donnees correspondantes.
Je peux passer les valeurs soit une par une (beurk), soit genre une List. Et c'etait ca le principe de la question:

• A partir d'une liste de valeurs fournie en argument, comment recuperer les donnes correspondants a ces cles primaires?

A defaut, je vais garder la table temporaire. Parce que la pipelined function ne correspond pas a mon cas.

Merci en tous cas

[mnitu]

Il y a encore

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
 
CREATE OR REPLACE Number_tt AS TABLE OF Number
/
declare
   a number_tt := number_tt();
   type t_data IS TABLE of tmp2.data%type;
   l_s t_data;
begin
   FOR i IN 1..100000 loop
      a.extend;
      a(i):=trunc(dbms_random.value(1,1000));
   end loop;
   --
   SELECT Data bulk collect INTO  l_s FROM tmp2 join (Select column_value val from table(a)) t On tmp2.n = t.val;
end;
/

[skuatamad]

A partir d'une liste de valeurs fournie en argument, comment recuperer les donnes correspondants a ces cles primaires?

Regarde varying-in-lists je pense que ça correspond à ton besoin, sinon je pense que c'est une des situations où la table temporaire a un sens.

[edit] Ah oui la proposition de mnitu me semble très intéressante

[Rams7s]

Bonjour,

Pour la fonction table:
Je pense qu'il y a une petite faute ligne 2 mnitu, create or replace type
Ca peut peut-etre servir a qulqu'un d'autre un autre jour.

Donc oui, pour la function table(...)ben pas mieux.

Tous les DB times ont ont augmente de moitie voir doubles.
Par contre, le temps CPU est plus ou moins le meme, 10% de difference maximum.

Dans certains des cas de tests menes, on a un peu plus d'enqueue avec la fonction table par rapport a la temporary table, mais rien de folochon.

Par contre, j'ai des physical read/writes avec la fonction table, chose que je n'avais pas du tout avec la table temporaire. C'est pour ca que la solution table est plus lente. Je sais pas trop trop pourquoi ils osnt la, mais ils sont presents a chaque fois.

Dans la majorite de mes cas de tests, la memoire PGA est plus faible (10 a 30%) avec la fonction table.Et il y a bien evidemment moins de table scans avec la fonction table.

Ce sera donc temporary table.

skuatamad, je regarderai ton lien plus en profondeur quand j'aurai du temps. Merci