File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes What could be the Alternative to Row Num to fetch result ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "What could be the Alternative to Row Num to fetch result ?" Watch "What could be the Alternative to Row Num to fetch result ?" New topic

What could be the Alternative to Row Num to fetch result ?

Azrael Noor
Ranch Hand

Joined: Jul 29, 2010
Posts: 384
Please find the following query:

Requirement: Records keep on coming into the database and we need to process it in serial they are coming and at a time, we need 10 records only.

Issue: DBA Client said the Rownum is giving some performance issues so find some alternative.

I am using Oracle 10G. What could be that on above query. Please guide

Azrael Noor
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

Yes, the issue is that the query gives some 10 more or less random rows. (Am I right?)

That is because of the way rownum works in Oracle. You need to create a query which will return you all rows in the order you desire, use that as an inner query and in the outer query limit the output to ten rows. If you don't have experience with inner queries, the syntax is like this:
Inner query is the one enclosed in parentheses, naturally.

In your case, the outer query will contain a where condition using rownum to get just ten rows.

Then there is a deeper, thornier issue: you seem to assume that the primary key of the table is assigned in an increasing sequence. In Oracle, that is hardly ever true. If the PK is assigned from a sequence, it can easily be assigned in "wrong" order. The best you can do is to add a timestamp column to the table and assign it a systimestamp when inserting data. Then you'll get the rows more or less ordered by their creation time. If the ID is generated externally and is guaranteed to be assigned in increasing order, this is not an issue for you.

Depending on your exact needs, you might still encounter difficulties, though. What you really want is to order columns rows by their "commit time", since that is when the row became visible to the other sessions. If a transaction that inserts a row get delayed, for example, and another ten insertions take place before the first one finishes, it may never appear in your query. Only you can know whether that is a real risk in your system, or whether it would matter at all. Note that even if you generate IDs externally as mentioned above, you may still be prone to this problem.

Edit: I've overlooked you mentioned performance problems. If you use rownum correctly (see above), then any performance problem should be easily amenable by adding an index on the column(s) you order the rows by. But keep in mind your current query gives wrong results, regardless of performance.

Edit 2: I've mentioned you need to order the rows by their commit time, but didn't tell you how to do that. The reason is that this is (as far as I know) not possible in Oracle.
I agree. Here's the link:
subject: What could be the Alternative to Row Num to fetch result ?
It's not a secret anymore!