Re: Rownum strange behaviour

On May 21, 3:57 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
i was wrong, maybe the explanation is more obvius, i think you always have to see
execution plan: dbms_random.value is recalculated for every value in the record set (add inv.idx to select list to see that values are not repeated

bye

 Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Mon May 21 2007 - 09:53:53 CDT