Oracle/ExaData RowNum Range returns duplicate rows

Question

I am executing this query on exadata in a for loop of a java program

select a, b, c, d from (
        select rownum r, a, b, c, d from foo order by c asc
) where r >= 40001 and r < 50001

Here i keep incrementing the numbers by 10000, so in the next loop iteration the numbers would be 50001 and 60001

I take the rows and insert into another database and loop again.

Currently, my code encounters random errors like

Exception in thread "main" java.sql.BatchUpdateException: Duplicate entry '23-ABC@XYZ.COM' for key 'PRIMARY'
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1809)
    at

When I check my source (exadata) database, there is only 1 row with a=23 and b = ABC@XYZ.COM. So the source doesn't have any duplication.

when I check my target database, I can see that the row a=23, b = ABC@XYZ.COM has already been inserted in a previous loop iteration. {{I drop and recreate the destination table at the start of the program.}}

So it looks like my window query is returning the same row again and again.

I did lot of search and I am pretty sure that my windowing query should not return duplicates... but it seems like it does

I am not an oracle/exadata expert... so let me know if there is a change that the query above can return the same row when run with different rownum ranges.

Or updates to the sort column. – Shannon Severance Aug 24 '16 at 16:22 — Shannon Severance, Aug 24 '16 at 16:22

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

Use row_number() over() that's actually a window function. Since a and b seems to make the record unique, you should try.

select a, b, c, d 
  from (
        select row_number() over (order by a,b) as r, 
                a, b, c, d 
          from foo
        ) 
    where r >= 40001 
      and r < 50001;

PS: Keep in mind that with this method there should not be any DML's over source table during load. PS2: rownum will never work in this case because is assigned before the order by. More info.

edited May 23 '17 at 12:01

Community

1
1

answered Aug 24 '16 at 16:30

vercelli

4,717
2
13
15

Yes, row_number() that's a windows function. rownum it is not – vercelli Aug 24 '16 at 16:43
DML includes inserts and deletes too. If there is a new row that will fall in the first position of the order by, you are going to have duplicates. If there's a delete on a row already transferred you're going to miss another row. – vercelli Aug 24 '16 at 16:48

Oracle/ExaData RowNum Range returns duplicate rows

1 Answers1