Early this morning, the business staff reported that the order data exported by the user was repeated and one order was missing.
Step 1: Query the data table. The actual data in the table is not duplicated or missing. Then it may be that the export process is wrong (because it is an asynchronous paging export, I first doubt this part of the code)
Step 2: Reproduce the problem (actually, the first step to reproduce the problem is to encounter production problems, but I forgot...)
Step 3: CheckAsynchronous paginationExporting the code is simply dirty and messy, but there is nothing wrong with running (most code is like this. After several waves of ravages from programmers of different qualities, even the tidy code will be messed up)
Step 4: Check the query statement
After the third step is fine, I actually suspected that it was a pagination problem.
Observing the exported data, it is found that the duplicate records are all on the boundary between the two pages. For example, Record A, it has a record on the second page and another record on the third page, but two identical records will not appear on the same page. .
The export page is sorted by the ORDER_DATE field, and the values are all 2025-01-13 00:00:00, and the hours, minutes and seconds are all 0.
If it is an order on the same day, then ORDER_DATE will be the same. The sorting is equivalent to invalid. The order of each query result is not fixed, which will lead to the pagination disorder.
On ROWNUM and Limiting ResultsThis issue is simulated in this article
-- Create tables and data
create table t as
select mod(level,5) id,
trunc(dbms_random.value(1,100)) data
from dual
connect by level <= 10000;
--The first query
select *
from (
select a.*, rownum rnum
from (
select id, data
from t
order by id
) a
where rownum <= 150
)
where rnum >= 148;
-- The second query
select *
from (
select a.*, rownum rnum
from (
select id, data
from t
order by id
) a
where rownum <= 151
)
where rnum >= 148;You will find that in the first and second query results, the same data of rownum is not the same, which means rownum is not fixed.
One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so.
The important thing in pagination query is that the ORDER BY statement should be unique, and if the order by field is not unique, then you should also add something to end up making it unique.