Re: Rownum strange behaviour

On May 21, 3:57 pm, mRangel <marcus.ran...@xxxxxxxxx> 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

.

Relevant Pages

  • Re: Rownum strange behaviour
    ... SQL> select countfrom user_objects; ... I am using Oracle 10.2.0.3 under RH ... from (select rownum idx, object_name from user_objects) inv ... and Oracle will use scalar subquery caching to run ...
    (comp.databases.oracle.server)
  • Re: Rownum strange behaviour
    ... table (for testing purposes). ... SQL> select countfrom user_objects; ... from (select rownum idx, ... Now, if you come to conclusion, it was executed more than one time, guess whether every execution of dbms_random.valueyield the same result or not. ...
    (comp.databases.oracle.server)
  • Re: How can this be ? (rownum question)
    ... SQL> select countfrom user_objects; ... from (select rownum idx, object_name from user_objects) inv ... from (select rownum idx, ...
    (comp.databases.oracle.server)
  • Re: Rownum strange behaviour
    ... table (for testing purposes). ... SQL> select countfrom user_objects; ... from (select rownum idx, ...
    (comp.databases.oracle.server)
  • How can this be ? (rownum question)
    ... table (for testing purposes). ... SQL> select countfrom user_objects; ... from (select rownum idx, ... RH linux. ...
    (comp.databases.oracle.server)