Problème avec dbms_random.value [Résolu]

[zoom61]

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 ?

[Waldar]

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.

[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

[Waldar]

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

[zoom61]

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