SELECT sur une liste de valeur

**Rams7s** · 13/05/2011, 17h56

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

Envoyé par Resultats

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** · 14/05/2011, 08h41

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** · 14/05/2011, 17h08

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** · 17/05/2011, 17h22

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** · 17/05/2011, 17h51

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** · 17/05/2011, 17h55

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** · 18/05/2011, 13h12

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

**mnitu** · 18/05/2011, 23h34

J'aime toujours quand les gens présent leur conclusions en oubliant de fournir leur test. Comme ça ils ont toujours raison ...

Mais je ne suis pas de tout d'accord avec vous.

Et voilà pour quoi

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
 
C:\app\Marius\diag\rdbms\diana\diana\trace>sqlplus mni/mni
 
SQL*Plus: Release 11.2.0.1.0 Production on Mer. Mai 18 22:22:13 2011
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connecté à :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> set timi on
SQL> Alter session set sql_trace = true
  2  /
 
Session modifiée.
 
Ecoulé : 00 :00 :00.01
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  /
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :00.76
SQL> declare
  2     a number_tt := number_tt();
  3     type t_data IS TABLE of tmp2.data%type;
  4     l_s t_data;
  5  begin
  6     FOR i IN 1..100000 loop
  7        a.extend;
  8        a(i):=trunc(dbms_random.value(1,1000));
  9     end loop;
 10     --
 11     SELECT DATA bulk collect INTO  l_s FROM tmp2 JOIN (SELECT column_value val FROM TABLE(a)) t ON tmp2.n = t.val;
 12  end;
 13  /
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :00.64
SQL> Alter session set sql_trace = false
  2  /
 
Session modifiée.
 
Ecoulé : 00 :00 :00.00
 
SQL> exit
 
tkprof diana_ora_9016.trc res.txt sort=execpu,fchcpu

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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
 
TKPROF: Release 11.2.0.1.0 - Development on Mer. Mai 18 22:23:48 2011
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: diana_ora_9016.trc
Sort options: execpu  fchcpu  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
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;
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.37       0.37          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.37       0.37          0          0          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91  
********************************************************************************
 
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;
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.35       0.37          0         20          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.35       0.37          0         20          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91  
********************************************************************************
 
SQL ID: 4ab77vtzfrd2s
Plan Hash: 2592595150
SELECT DATA 
FROM
 TMP2 JOIN (SELECT COLUMN_VALUE VAL FROM TABLE(:B1 )) T ON TMP2.N = T.VAL
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.02          0          0          0           0
Fetch        1      0.20       0.23          0        266          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.21       0.26          0        266          0      100000
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  HASH JOIN  (cr=266 pr=0 pw=0 time=236186 us cost=349 size=2100021 card=100001)
 100000   COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=191046 us cost=30 size=200000 card=100000)
 100000   TABLE ACCESS FULL TMP2 (cr=266 pr=0 pw=0 time=213424 us cost=78 size=2231037 card=117423)
 
********************************************************************************
 
SQL ID: 0q1fub3bgsvzm
Plan Hash: 1066105302
SELECT DATA 
FROM
 TMP2 INNER JOIN TMP2_TEMPORARY ON N=N2
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.20       0.23          0        421          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.20       0.23          0        421          0      100000
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  HASH JOIN  (cr=421 pr=0 pw=0 time=940527 us cost=544 size=5521408 card=172544)
 100000   TABLE ACCESS FULL TMP2 (cr=266 pr=0 pw=0 time=181200 us cost=78 size=2231037 card=117423)
 100000   TABLE ACCESS FULL TMP2_TEMPORARY (cr=155 pr=0 pw=0 time=195778 us cost=85 size=2243059 card=172543)
 
********************************************************************************
 
SQL ID: adt7d86zxyrfm
Plan Hash: 0
INSERT INTO TMP2_TEMPORARY 
VALUES
 (:B1 )
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.09       0.14          0        257       1160      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.09       0.14          0        257       1160      100000
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=257 pr=0 pw=0 time=0 us)
 
********************************************************************************

Les deux méthodes sont comparable et de plus je ne vois pas de trace des vos "physical read/writes avec la fonction table, chose que je n'avais pas du tout avec la table temporaire" etc.

**Rams7s** · 19/05/2011, 16h30

Je pense que j'ai l'air malin si je dis j'arrive pas a reproduire les resultats, non?

Ceci etant dit, j'ai recommence. Et les resultats (les nouveaux) ne correspondent pas trop du tout aux anciens. Ce qui m'ennuie profondement, parce que j'ai du sacrement m'embrouiller quelque part du coup, et que je dois verifier deux,trois autres trucs.

Donc oui ben non, la fonction table(...) fonctionne mieux en faits.

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
 
create or replace type dbType_key is table of number;
/
 
create or replace package nosql_package is
   type t_nosql is table of nosql_table%rowtype;
   type t_key is table of nosql_table.key%type;
   type t_data is table of nosql_table.data%type;
 
 howmanyreads number;
   howmanyhotblocks number;
   stairsize number;
   table_size number;
   param_set number;
 
   procedure init;
 
   function get_data_temporary_table(key_tab t_key) return t_data;
   function get_data_table_func(key_tab dbType_key) return t_data;
end nosql_package;
/
show errors
 
 
create or replace package body nosql_package is
   procedure init is
[...]
end;
 
   function get_data_temporary_table(key_tab t_key) return t_data is
      l_data t_data;
   begin 
      forall i IN key_tab.first..key_tab.last INSERT INTO nosql_tmp VALUES (key_tab(i));
      SELECT DATA bulk collect INTO  l_data FROM nosql_table INNER JOIN nosql_tmp ON nosql_table.key=nosql_tmp.key;
      return l_data;
   end;
 
   function get_data_table_func(key_tab dbType_key) return t_data is
      l_data t_data;
   begin
      select data bulk collect into l_data from nosql_table inner join (select column_value val from table(key_tab)) t on key=val;
      return l_data;
   end;
end;
/
show errors
 
exec testing_pkg.snap_start('reandRandom -values temp table');
declare
   l_datas nosql_package.t_data:=nosql_package.t_data();
   l_keys nosql_package.t_key:=nosql_package.t_key();
begin
   for i in 1..nosql_package.howmanyreads loop
      l_keys.extend();
      l_keys(i):=trunc(dbms_random.value(1,nosql_package.table_size));
   end loop;
   l_datas:=nosql_package.get_data_temporary_table(l_keys);
   dbms_output.put_line(l_datas.count);
end;
/
exec testing_pkg.snap_stop
 
exec testing_pkg.snap_start('reandRandom -values table function');
declare
   l_datas nosql_package.t_data:=nosql_package.t_data();
   l_keys dbType_key:=dbType_key();
begin
   for i in 1..nosql_package.howmanyreads loop
      l_keys.extend();
      l_keys(i):=trunc(dbms_random.value(1,nosql_package.table_size));
   end loop;
 
   l_datas:=nosql_package.get_data_table_func(l_keys);
   dbms_output.put_line(l_datas.count);
 
end;
/
exec testing_pkg.snap_stop

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
TEST_NAME                           NAME                           VALUE
----------------------------------- ------------------------- ----------
reandRandom -values table function  CPU used by this session         407
reandRandom -values temp table      CPU used by this session         442
reandRandom -values table function  DB time                          416
reandRandom -values temp table      DB time                          725
reandRandom -values table function  session logical reads          18013
reandRandom -values temp table      session logical reads          51411
reandRandom -values temp table      user I/O wait time               252
reandRandom -values table function  user calls                         6
reandRandom -values temp table      user calls                         6
reandRandom -values table function  enqueues                          16
reandRandom -values temp table      enqueues                         813

Avec:
howmanyreads 1000000
table_size 5000000

SELECT sur une liste de valeur

PL/SQL Oracle

Discussions similaires

Partager

Partager