Oracle pagination query sorting data duplicate problem

Little scum · Posted on 9/20/2017 9:52:47 AM

Pagination queries are used a lot during project development, and when you want to sort the data by date, there is a lot of duplicate data.

This problem occurs because the value of the date is not unique in the data.

We know

Oracle's pagination query does not perform any sorting operations, Oracle reads the eligible data from the data block sequentially and returns it to the client.

Oracle's sorting algorithm is not stable, that is, for key-equal data, this algorithm does not guarantee that the key-equal data will maintain the order before sorting after the algorithm completes the sorting.

The solution to this problem is to add a unique column at the end, such as the primary key, to solve the problem.

Login is visible.

or

Login is visible.

Little scum · Posted on 9/25/2017 4:00:51 PM

The difference between rownum and row_number() in Oracle
http://www.itsvse.com/thread-4107-1-1.html
(Source: Architect)

Little scum · Posted on 9/25/2017 4:02:34 PM

A rownum is assigned to a row after where condition filtering and before any order by or aggregation. At the same time, the rownum will only be incremented when it is assigned to a row. The initial value of rownum is 1. rownum no longer changes after being generated in the query:
select * from emp where ROWNUM <= 5 order by sal desc;
The purpose of this statement is to return the information of the top 5 highest-paid employees, but according to the principle of rownum, rownum has been generated before order by, so this statement does not play the role of top 5, the correct syntax is as follows:
select * from (select * from emp order by sal desc) where ROWNUM <= 5;

[Source] Oracle pagination query sorting data duplicate problem

Related Posts

Sections viewed