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


hallo,
try
set autotrace on
You see that user_objects is a view on an union
that explains duplicated rownum (interesting to know)

bye
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com

.

Relevant Pages

  • Re: Translate week number to date of the week.
    ...    Is there a quick way to convert week number to start ... RRRR') + rownum - 1 dt ... SQL> with date_wk as ( ... Reporting the dates having the desired week of the year ...
    (comp.databases.oracle.server)
  • Re: Full Text Search Timing Out with only 20 total users
    ... these sorts of performance problems. ... Looking for a SQL Server replication book? ... I am having a major issue with search and fulltext indexes. ... ,RANKOVER AS RowNum ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Select enumeration of intergers
    ... connect by rownum < 1000 ... Some years since I knew any SQL but I got a kick out of the exchange where Mikito said that UNION should be identical to OR and the Oracle 'oracle' gave an example where it's not. ... It also would allow a way to define a kind of enumeration of a finite domain/data type in a way that none of these products seem to, ...
    (comp.databases.theory)
  • Regarding some update SQL with a running sequence
    ... column of a table depending on its rownum given a particular order by ... EMPNO SAL NUM ... Could anybody please suggest some workaround SQL for this problem? ...
    (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)