Re: Rownum strange behaviour

From: Maxim Demenko <mdemenko@xxxxxxxx>
Date: Mon, 21 May 2007 17:02:00 +0200

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
.

References:
- Rownum strange behaviour
  - From: mRangel

Prev by Date: Re: SQL Tuning Regarding System CPU Stats
Next by Date: Performance problem hard to solve
Previous by thread: Re: Rownum strange behaviour
Next by thread: Performance problem hard to solve
Index(es):
- Date
- Thread

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, ... the record set (add inv.idx to select list ...
(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)