On May 21, 3:52 pm, mRangel <marcus.ran..._at_gmail.com> wrote:
> 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
>
> SQL>
It seems to be as follows:
the call to dbms_random will be performed for every line from
user_objects that needs to be filtered in the where-clause.
So every row (and rownum) has a new chance of finding a match, and
indeed sometimes does.
Try running the following for more insight into the problem:
select inv.idx
, foo
, inv.object_name
from ( select rownum idx, object_name from all_objects where rownum
<= 600) inv
cross join (select 1 + trunc(dbms_random.value * 600) as foo from
dual)
Regards,
Erik Ykema
Received on Wed May 23 2007 - 01:46:57 CDT
