Re: Rownum strange behaviour

mRangel schrieb:
> I am trying to write a little piece of code to get "any record" from a
> table (for testing purposes). I wrote the following:
>
> SQL> select count(1) from user_objects;
>
> COUNT(1)
> ----------
> 617
>
> SQL> select inv.object_name
> 2 from ( select rownum idx, object_name from user_objects ) inv
> 3 where inv.idx = 1 + trunc(dbms_random.value * 600)
> 4 /
>
>
> Much to my surprise, as you can see below, the number of lines
> returned varies from 0 to N ! How is it possible ? Can ROWNUM be
> repeated inside the inline view ? I am using Oracle 10.2.0.3 under RH
> linux.
>
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
> HR_CODIF_OPER_RESUMO
> IDX_PERF0015
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
> WB_MI_TMP_DUPLICS
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
> DBG$$$_SPLIT_3_GRP1
> IDX_PERF0066
> MR_PESSOAS_JURIDICAS
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
> WB_HOUR12MI_SS
>
> SQL> /
>
> OBJECT_NAME
> --------------------------------------------------------------------------------
> MG_FERIADOS
> IDX_PERF0072
>

Guess, how many times dbms_random.value() was executed on your resultset   consiting of 617 rows.
Now, if you come to conclusion ( as per accident), it was executed more than one time, guess whether every execution of dbms_random.value() yield the same result or not.

Best regards

Maxim Received on Mon May 21 2007 - 10:02:00 CDT