Home » SQL & PL/SQL » SQL & PL/SQL » Rownumer WITH OUT Order by (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Rownumer WITH OUT Order by [message #672046] Sat, 29 September 2018 08:55 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

We are using the following pagination query to distribute the result set into multiple files.
Here the problem is
some times few records are duplicating and some times few records are missing in few files .

We are not using the order by clause in the query.I am suspecting because of this we cann't expect the
same rownumber for each and every records for all executions.

We have 10 files and each file will contains nearly 15K records.Every time we will fire same query with different lower and upper boundary .

SELECT *
FROM
  (SELECT PAGINATED_TBL.*,
    ROWNUM RNUM
  FROM
    (SELECT * FROM MAIN_TRXN MV
    ) PAGINATED_TBL
  WHERE ROWNUM <= &upper boundary
  )
WHERE RNUM        >= &lower boundary
AND BRANCH_GROUP_ID='ABC';
But when i am testing not able to replicate the issue.I am suspecting the issue is not using the ORDER BY clause.Please Confirm

My final question is can we expect the same order every time if we are not using any order by clause

Thanks in advance


[EDITED by LF: applied [code] tags]

[Updated on: Sat, 29 September 2018 09:31] by Moderator

Report message to a moderator

Re: Rownumer WITH OUT Order by [message #672047 is a reply to message #672046] Sat, 29 September 2018 09:00 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
You know how to use [code] tags. Please use them. Also, please stop saying "record" and "file" when you mean (I think) "row" and "table". Lastly, you have not given any description of the tables, the data, the result you want, or the result you are getting.

Better start again.
Re: Rownumer WITH OUT Order by [message #672052 is a reply to message #672046] Sat, 29 September 2018 09:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
No you can't. Only ORDER BY (or implicit order by in hierarchical queries) guarantees row order.

SY.

Re: Rownumer WITH OUT Order by [message #672053 is a reply to message #672047] Sat, 29 September 2018 09:39 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John,

Thanks for correcting me.I will follow the same next on words.
My result set contains 60 columns and 5 lacks rows.We are exporting the all rows of table into multiple text files using the above query.

Thanks Solomon for the confirmation.If I use the order by while generating the row number i think my issue will be resolved.

Thanks
Revathi.T



Re: Rownumer WITH OUT Order by [message #672054 is a reply to message #672053] Sat, 29 September 2018 10:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
And here is example on 4 node RAC:

with PAGINATED_TBL AS (
                       SELECT  *
                        FROM  DUAL
                        CONNECT BY LEVEL <= 100000
                      ),
                X AS (
                      SELECT  /*+ MATERIALIZE */
                              PAGINATED_TBL.*,
                              ROWNUM RNUM
                        FROM  PAGINATED_TBL
                       WHERE ROWNUM <= 12345
                     ) 
 SELECT  /*+ PARALLEL(8) */
         X.*
  FROM  X
  WHERE RNUM >= 12
/

D       RNUM
- ----------
X       5923
X       5924
X       5925
X       5926
X       5927
X       5928
X       5929
X       5930
X       5931
X       5932
X       5933
...
X       6580
X      12294
X      12295


D       RNUM
- ----------
X      12296
X      12297
X      12298
X      12299
X      12300
X      12301
X      12302
X      12303
X      12304
X      12305
X      12306


D       RNUM
- ----------
X      12307
X      12308
X      12309
X      12310
X      12311
X      12312
X      12313
X      12314
X      12315
X      12316
X      12317


D       RNUM
- ----------
X      12318
X      12319
X      12320
X      12321
X      12322
X      12323
X      12324
X      12325
X      12326
X      12327
X      12328


D       RNUM
- ----------
X      12329
X      12330
X      12331
X      12332
X      12333
X      12334
X      12335
X      12336
X      12337
X      12338
X      12339


D       RNUM
- ----------
X      12340
X      12341
X      12342
X      12343
X      12344
X      12345
X       9213
X       9214
X       9215
X       9216
X       9217
...

D       RNUM
- ----------
X      10471
X      10472
X      10473
X      10474
X      10475
X      10476
X      10477
X      10478
X      10479
X      10480
X      10481


D       RNUM
- ----------
X      10482
X      10483
X      10484
X       2633
X       2634
X       2635
X       2636
X       2637
X       2638
X       2639
X       2640
...

D       RNUM
- ----------
X       3279
X       3280
X       3281
X       3282
X       3283
X       3284
X       3285
X       3286
X       3287
X       3288
X       3289


D       RNUM
- ----------
X       3290
X         12
X         13
X         14
X         15
X         16
X         17
X         18
X         19
X         20
X         21

SY.
Re: Rownumer WITH OUT Order by [message #672056 is a reply to message #672054] Sat, 29 September 2018 11:17 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member

Thank you very much Solomon for providing the detailed query with problem replication .

Thanks
SaiPradyumn
Re: Rownumer WITH OUT Order by [message #672060 is a reply to message #672046] Sun, 30 September 2018 11:35 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Solomon ,
In the example which you had provided we are generating the rows on fly & there is no permanent table .

If we split our query into two parts like below:

1:Table creation
2: Row generation

CREATE TABLE MAIN_TRXN AS
SELECT * FROM DUAL CONNECT BY LEVEL <= 100000;

SELECT *
FROM
  (SELECT PAGINATED_TBL.*,
    ROWNUM RNUM
  FROM
    (SELECT * FROM MAIN_TRXN MV
    ) PAGINATED_TBL
  WHERE ROWNUM <= 55000
  )
WHERE RNUM >= 54000;


Then also can we replicate the problem ?

SaiPradyumn
Re: Rownumer WITH OUT Order by [message #672061 is a reply to message #672060] Sun, 30 September 2018 12:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
Again, only ORDER BY guarantees row order:

SQL> create table PAGINATED_TBL AS (
  2                         SELECT  *
  3                          FROM  DUAL
  4                          CONNECT BY LEVEL <= 100000
  5                        )
  6  /

Table created.

SQL> set pause on
SQL> with X AS (
  2                        SELECT  /*+ MATERIALIZE */
  3                                PAGINATED_TBL.*,
  4                                ROWNUM RNUM
  5                          FROM  PAGINATED_TBL
  6                         WHERE ROWNUM <= 12345
  7                       ) 
  8   SELECT  /*+ PARALLEL(8) */
  9           X.*
 10    FROM  X
 11    WHERE RNUM >= 12
 12  /


D       RNUM
- ----------
X       5923
X       5924
X       5925
X       5926
X       5927
X       5928

As you can see RNUM doesn't start with 12. When optimizer decides to run query in parallel it creates master process and multiple slave processes where master gives each slave a subset row to work with. Each slave returns result set and master then combines them into final result set. So even though each slave might return rows order by some column (either because index or say analytic function was used) combining such ordered sets might not result in ordered final result set simply because slave might finish their part in any sequence like it happened in example I posted. So I'll repeat again, only ORDER BY guarantees row order.

SY.
Re: Rownumer WITH OUT Order by [message #672071 is a reply to message #672061] Mon, 01 October 2018 08:19 Go to previous messageGo to next message
Bill B
Messages: 1870
Registered: December 2004
Senior Member
Look into using the analytic ROW_NUMBER instead of ROWNUM. ROW_NUMBER is after order by is processed and ROWNUM is before.

[Updated on: Mon, 01 October 2018 08:19]

Report message to a moderator

Re: Rownumer WITH OUT Order by [message #672072 is a reply to message #672071] Mon, 01 October 2018 08:24 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
row_number still needs an order by to produce consistent results - it goes in the analytic itself rather than at the end, but the problem remains.
Re: Rownumer WITH OUT Order by [message #672169 is a reply to message #672046] Fri, 05 October 2018 03:02 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All

Thanks for your suggestion to understand the problem.So to avoid this duplication and missing rows in the pagination query
we are giving the following solution :

1:Created the sequence
2:Added one more extra column in the table and inserted the unique value with the help of sequence.

We need to report only existing fields to the final output.Newly added the extra field should not be reported.


Final query is :


SELECT *
FROM
  (SELECT PAGINATED_TBL.*,
    ROWNUM RNUM
  FROM
    (SELECT COL1
      || COL2
      || COL3
      || COL4
      || COL5
      || COL6
      || COL7
      || COL8
      || COL9
      || NAMES
    FROM PAGINATED_TBL MV
    ORDER BY UNIQUE_IDENTIFER
    ) PAGINATED_TBL
  WHERE ROWNUM <=85236
  )
WHERE RNUM >= 456;


drop table  PAGINATED_TBL;
DROP SEQUENCE SEQ; 
CREATE SEQUENCE  SEQ START WITH 1 INCREMENT BY 1  ;
CREATE TABLE PAGINATED_TBL AS
  ( SELECT  to_char(to_date(level, 'j'),'jsp') NAMES , SEQ.NEXTVAL UNIQUE_IDENTIFER  
  FROM DUAL CONNECT BY LEVEL <= 1000000
  ) ;
 ALTER TABLE PAGINATED_TBL ADD (COL1 VARCHAR2(20) ,  COL2 VARCHAR2(20) , COL3 VARCHAR2(20));
 ALTER TABLE PAGINATED_TBL ADD (COL4 VARCHAR2(20) ,  COL5 VARCHAR2(20) , COL6 VARCHAR2(20));
 ALTER TABLE PAGINATED_TBL ADD (COL7 VARCHAR2(20) ,  COL8 VARCHAR2(20) , COL9 VARCHAR2(20));


Major change is adding the ORDER by in the first sub query .
Here the problem is what ever the columns we are reporting earlier,those only we need to report.
That's why still depending on rownum to generate the sequence numbers in second sub query
In final query using the same (RNUM) to get the N numbers rows every time.

When I am testing this scenario its giving the proper results.
Here my questiuon is can we trust this rownum always in the second sub query,as we are getting the order results form the first query always.
will the same order is propagated to final query also ?

Thanks
SaiPradyumn





Re: Rownumer WITH OUT Order by [message #672170 is a reply to message #672169] Fri, 05 October 2018 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes - that's how pagination queries work.
The inner query gives an ordered set of data, rownum is applied to the data as it's returned from the inner query - in order.
Re: Rownumer WITH OUT Order by [message #672174 is a reply to message #672170] Fri, 05 October 2018 04:19 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks cookiemonster for the conformation.
As i can't to put the ROWNUM in the first query, I got his doubt.
The very first query doesn't have the ROWNUM but have order by unique identifier,
Second query is generating rownum less than highest boundary and assigning unique values to Rnum .
ans third query we are restricting with lowest boundary.


One thanks you very much for the help Smile

Thanks
SaiPradyumn
Re: Rownumer WITH OUT Order by [message #672448 is a reply to message #672174] Mon, 15 October 2018 14:33 Go to previous message
Bill B
Messages: 1870
Registered: December 2004
Senior Member
Below is a pagination query that uses row_number. The beauty of using an analytic is that the data is only looked at once. with rownum you have sort everything and then apply the filter. it takes much more time to do it.
SELECT Owner, Object_name, Object_type
FROM (SELECT Owner,
             Object_name,
             Object_type,
             ROW_NUMBER() OVER (ORDER BY Owner, Object_name) Rn
      FROM All_objects)
WHERE Rn BETWEEN 100 AND 132
ORDER BY Rn;
Previous Topic: Convert Date in String field (merged)
Next Topic: Recursive query
Goto Forum:
  


Current Time: Fri Jul 19 10:16:34 CDT 2019