Re: Rownum strange behaviour

"mRangel" <marcus.rangel_at_gmail.com> wrote in message news:1179755835.514338.171910_at_b40g2000prd.googlegroups.com...
>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
>

With your construction, dbms_random is being called once for every row in the instantiated in-line view. If you selected the idx column you would see that it had different values in it when you reported multiple rows.

Change the query to:

select inv.object_name
from ( select rownum idx, object_name from user_objects ) inv where inv.idx = (select 1 + trunc(dbms_random.value * 600) from dual) /

and Oracle will use scalar subquery caching to run the DUAL subquery once - then pick just one row.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html