Problème avec dbms_random.value

Version imprimable

Voici en gros mon code :

Code:

1
2
3
with lst as (select ... from ...),
  val as (select trunc(dbms_random.value*5) nb from dual where rownum = 1)
select lst.*, val.* from lst, val

Dans ce cas la valeur val.nb est fixe.

Mais si je mets un filtre sur cette valeur val.nb varie :

Code:

1
2
3
with lst as (select ... from ...),
  val as (select trunc(dbms_random.value*5) nb from dual where rownum = 1)
select lst.*, val.* from lst, val where lst.rdm < val.nb

Avez-vous une idée ?

Tient oui c'est étrange, sur une 11.1.0.7.0 :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
With SR as
(
SELECT trunc(dbms_random.value * 10) nb
  FROM dual
 WHERE rownum = 1
)
select sr.nb, ao.object_id
  from sr, all_objects ao
 where ao.object_id < sr.nb;
 
        NB  OBJECT_ID
---------- ----------
         9          2
         1          3
         1          4
         1          5
         1          6
         1          7
         1          8

Le NB varie mais que sur la première ligne.
Je constate le même comportement sans le rownum = 1.

Par contre si on écrit explicitement le cross join, ça a l'air fixe :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
With SR as
(
SELECT trunc(dbms_random.value * 20) nb
  FROM dual
 WHERE rownum = 1
)
select sr.nb, ao.object_id
  from sr cross join all_objects ao
 where ao.object_id < sr.nb;
 
        NB  OBJECT_ID
---------- ----------
        11          3
        11          9
        11          7
        11          6
        11          5
        11          8
        11         10
        11          2
        11          4

Il faut par contre garder aussi le rownum = 1, si je le supprime le NB est ré-évalué à chaque ligne.

17/05/2011, 16h51
skuatamad

Il faut regarder le plan voir si Oracle materialise bien le WITH.
Normalement le côté spécial de rownum fait que le WITH est matérialisé, mais est ce du 100% ? je ne sais pas.
Qu'est ce que ça donne avec le hint materialize ?

Sinon voir cette discussion très intéressante sur asktom concernant dbms_random.value
17/05/2011, 17h14
Waldar

Bien vu, avec le hint le random n'est bien calculé qu'une seule fois.

Cela ne fonctionne pas...

Par contre cela fonctionne correctement :
Code:

1 2 3 4 select ... from ... where ... and rownum between to_number(floor(dbms_random.Value(1,1000))) AND 10