• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 385
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic