| Rownumer WITH OUT Order by [message #672046] |
Sat, 29 September 2018 08:55  |
 |
saipradyumn
Messages: 419
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';
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  |
John Watson
Messages: 8836
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 #672054 is a reply to message #672053] |
Sat, 29 September 2018 10:05 |
Solomon Yakobson
Messages: 3227
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 #672061 is a reply to message #672060] |
Sun, 30 September 2018 12:39 |
Solomon Yakobson
Messages: 3227
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 #672169 is a reply to message #672046] |
Fri, 05 October 2018 03:02 |
|
saipradyumn
Messages: 419
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 |
cookiemonster
Messages: 13904
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 #672448 is a reply to message #672174] |
Mon, 15 October 2018 14:33 |
Bill B
Messages: 1971
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; |
|
|
|
] 
Search
Help
Members
Register
Login
Home
