Rownum for large rownumbers

[Jayesh A Lalwani]

We have this table that contains 200K records. We have a CPU intensive process that processes the 200K records an generates 60 million records. To make this scalable, we dividee e 200K records into batches of 100 and execute it on the Grid. There is a master node that takes the request, finds the number records, divides by 100 to find number of batches, and creates the batches to be executed on the grid. The grid has several workers that pull one batch at a time out of the queue, retrieve the rows for the batch, execute them and save the results back to the Database.

So, the worker retreives the rows like this

select * from
  (Select *
  from mytable
  where taskid=?)
where rownum > ? and rownum <= ?

In java code

PreparedStatement ps = connection.createStatement(sql);
ps.setInt(1, taskId);
ps.setLong(2, batchNumber*batchSize);
ps.setLong(3, (batchNumber+1)*batchSize)

This was working fine till we were executing for 100K records. But, once we start executing for 200K records, we see that we don;t get the exact rows that we want. It's almost like Oracle is guessing which row we want and gets the rows that are 2-3 rows away from the rows that we are looking for. For example, last night, for batch 1453, instead of giving rows 145301 - 145400, it gave 145304 - 145403. Batch 1454 gave 145401 - 145500. As a result loans 145401-145403 got executed in 2 batches, and it gave a Constraint violation exception

Is Oracle inaccurate with very high rownumbers? Is it a good practice to use rowNum for batching?

[Martin Vashko]

Rownums are accurate. You just need to keep in mind the rownums are assigned to the rows as the resultset is being generated. It has these consequences:

1) You need to include an ORDER BY in your inner query, otherwise the rows can come in arbitrary order and rows might get assigned different numbers in your processing. Maybe you left that out for simplicity, but it is a crucial piece of code in this situation.

2) If the underlying tables change between the runs of the query, a new row can appear in the resultset, or a previously existing row can no longer be there. This can shift the numbers, of course. You can avoid this using flashback in some situations.

3) Oracle will need to materialize all of the rows in the query up to the highest requested row number. So for the first batch, it will materialize 100 rows, but 200 for the second, 300 for the third and so on. Using pagination for this large amount of rows is probably quite ineffective.

Oracle has some support for parceling out the table for batched processing based on rowids; this has the added benefit that processed rows are physically grouped, which can lead to less physical IO. Search for oracle rowid based parallelism for more details.

-----------------
Edit: where does the row number you compare to the expected row number come from? This is not clear from your example. Also note that the pseudocode must look like this (but it does, otherwise you wouldn't get anything for the second and subsequent batches):
select * from (Select rownum rn, t.* from mytable t where taskid=? order by xxxxx) -- !!! where rn > ? and rn <= ?

[Jayesh A Lalwani]

Martin Vajsar wrote:Rownums are accurate. You just need to keep in mind the rownums are assigned to the rows as the resultset is being generated. It has these consequences:

1) You need to include an ORDER BY in your inner query, otherwise the rows can come in arbitrary order and rows might get assigned different numbers in your processing. Maybe you left that out for simplicity, but it is a crucial piece of code in this situation.

We don;t have an order by in the inner query. We were getting the rows on natural order. I can try that. However, does the natural order change ?

Martin Vajsar wrote:

2) If the underlying tables change between the runs of the query, a new row can appear in the resultset, or a previously existing row can no longer be there. This can shift the numbers, of course. You can avoid this using flashback in some situations.

Underlying tables don't change. Once the rows are assinged to a task, we don't change the assignment ever. User has to load more data.

Martin Vajsar wrote:

3) Oracle will need to materialize all of the rows in the query up to the highest requested row number. So for the first batch, it will materialize 100 rows, but 200 for the second, 300 for the third and so on. Using pagination for this large amount of rows is probably quite ineffective.

Yes, I was worried about that but when we had done some analysis with like 10K rows, it wasn't a significant impact. We run Monte Carlo simulations in Java which take a long time, and the time required to retrieve data is insignificant compared to the time to run MonteCarlo simulation. So, we decided to live with the hit, instead of optimizing a high hanging fruit. Now, that it doesn;t work, I am thinking maybe we should do something else like assigning rows to batch when we assign it to task

Martin Vajsar wrote:


Edit: where does the row number you compare to the expected row number come from? This is not clear from your example. Also note that the pseudocode must look like this (but it does, otherwise you wouldn't get anything for the second and subsequent batches):
select * from (Select rownum rn, t.* from mytable t where taskid=? order by xxxxx) -- !!! where rn > ? and rn <= ?

Yes you are right. It looks like that except that the order by is missing. It's actually a larger query because this is an inner query that is joined with other tables. I didn;t want to post the whole thing because it might be confusing.

[chris webster]

I've found that ROWNUM can be a little unpredictable in complex queries, as it may sometimes be generated at a different stage in the query processing from what you might expect.

You could look at the Oracle built-in analytic function ROW_NUMBER instead, which will give you an output row number in your SELECT but allows you to specify exactly where the row number should be generated and how the data should be ordered within the SELECT clause.

As an aside, have you thought about doing this differently? Could you write the whole thing in SQL so you are not retrieving the data from the DB at all? Also, are you using partitioning to help spread the load when fetching the data?

Finally, remember that there is no "natural order" to the data in your tables. There is just the physical order the records are held in the table, and the order you specify when selecting them. If you don't specify an order, there is no guarantee that the data will be fetched in the same order if you repeat the same query on the same data.

[Martin Vashko]

If you don't specify an order by, the database can return rows in any order it likes. Granted, order of rows should not change if data didn't change and query plans didn't change. But since you're parametrizing the query with different parameters, the plan actually could change. It is probably not the reason for your current behavior, but in any case, use the ORDER BY. It can save you headaches in the long run.

I'll reiterate: where does the row number you compare to the expected row number come from?

[Martin Vashko]

chris webster wrote:I've found that ROWNUM can be a little unpredictable in complex queries, as it may sometimes be generated at a different stage in the query processing from what you might expect.

Are you speaking about an Oracle bug, or rather about a user's mistake caused by the complexity of the query and/or the lack of understanding?

[chris webster]

Martin Vajsar wrote:

chris webster wrote:I've found that ROWNUM can be a little unpredictable in complex queries, as it may sometimes be generated at a different stage in the query processing from what you might expect.

Are you speaking about an Oracle bug, or rather about a user's mistake caused by the complexity of the query and/or the lack of understanding?

Not a bug, just the occasional tendency to misunderstand where the ROWNUM gets created in relation to the ORDER BY in some more complicated queries. Can't remember the details now, but I know we found it much easier to use ROW_NUMBER() to give us an explicit point in the process where we knew our row number was being generated and how, and it was more flexible as well.

[Martin Vashko]

chris webster wrote:Not a bug, just the occasional tendency to misunderstand where the ROWNUM gets created in relation to the ORDER BY in some more complicated queries. Can't remember the details now, but I know we found it much easier to use ROW_NUMBER() to give us an explicit point in the process where we knew our row number was being generated and how, and it was more flexible as well.

Ok, got it. Thanks for clarification.

When the query is complicated, it might be better to create a view and then paginate on that view instead, the pagination query will be clear and plain then (or as clear and plain as it gets in Oracle ).

[Jayesh A Lalwani]

chris webster wrote:I've found that ROWNUM can be a little unpredictable in complex queries, as it may sometimes be generated at a different stage in the query processing from what you might expect.

You could look at the Oracle built-in analytic function ROW_NUMBER instead, which will give you an output row number in your SELECT but allows you to specify exactly where the row number should be generated and how the data should be ordered within the SELECT clause.

Thanks I can try this

As an aside, have you thought about doing this differently? Could you write the whole thing in SQL so you are not retrieving the data from the DB at all? Also, are you using partitioning to help spread the load when fetching the data?

Can;t do Monte Carlo stimulations in DB. Even if we can do it using PL/SQL, it's going to be much more scalable to do it on the grid. Seriously, these are some intense computations. If you run it on a single thread it will take days. The goal is to execute the simulation on 80 threads and get it down to 1 hour

We aren't partitioning the data. Hmm.. If I pre-assign the batches to the records instead of using rownnum, I might be able to partition by batch, which might make things a lot faster.

Finally, remember that there is no "natural order" to the data in your tables. There is just the physical order the records are held in the table, and the order you specify when selecting them. If you don't specify an order, there is no guarantee that the data will be fetched in the same order if you repeat the same query on the same data.

Martin Vajsar wrote:If you don't specify an order by, the database can return rows in any order it likes. Granted, order of rows should not change if data didn't change and query plans didn't change. But since you're parametrizing the query with different parameters, the plan actually could change. It is probably not the reason for your current behavior, but in any case, use the ORDER BY. It can save you headaches in the long run.

Right! I'll try an Order By too

I'll reiterate: where does the row number you compare to the expected row number come from?

Maybe I'm not understanding the question. Each batch has 2 parameters, batchNumber and batch Size. When the worker executes

long startRow=(long)batchNumber*batchSize; 
endRow = (long)(batchNumber+1)*batchSize.
ps.setInt(1, taskId);
ps.setInt(2, startRow);
ps.setInt(3, endRow);

[Martin Vashko]

Using batch id will let you get rid of the pagination query. Partitioning by batch id will additionally allow Oracle to full-scan individual partitions. But as the reading performance is apparently not the most pressing issue, you might try it without partitioning first. After all, managing 2000 partitions (200K records, batch size 100) can be tedious. Edit: forget that sentence. You could use hash partitioning or some other scheme, of course.

Back to my question: your query picks up some records in some order and parcels them to individual batches. How do you know the records are not properly aligned? You wrote:

Jayesh A Lalwani wrote:For example, last night, for batch 1453, instead of giving rows 145301 - 145400, it gave 145304 - 145403.

How do you know the numbers of these rows? Do you read them from the resultset?

You might also try putting the complicated query into a view and paginate that. You'd be then able to post the whole pagination query here so that we could see whether there is something wrong with it.

[Jayesh A Lalwani]

Ahh ok. I am looking at the business key of the rows returned.

The symptom that this problem exhibited was that we ran our processing with 200K records, we found that it was randomly giving ConstraintViolations when it inserted to result into the database. There can be only one output record for every input record, and the output tables have unique constraints on the business key. That meant that the same record is being executed twice. Initially, I thought the code is not thread safe, and somehow records that are being executed in one thread are migrating into another thread. SO, I added logging, and I log the business key of the records at several points in the processing. While looking at the logs, I saw that the record with the same business key was being executed in 2 differrent Java processes (actually the 2 processes are on 2 differrent machines).

So, it;s not a thread-safety issue (which is good.. because I was sweating bullets when I suspected thread safety). I found the batches that were colliding in the logs. I ran the query that retreives the records in SQL Developer, and one of the batches gets the wrong records.