posted 11 years ago
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.