Seeking advise on 8i, rownum, order by and indexes

Hi everyone,

I have a database running on oracle 8i.

I have a set of three tables with 1:1 related rows. Each table has about 350,000 rows and this grows by about 600 per day.

I have a unique numerical column named id_pub that is assigned from a sequence each time a row is created - this is not the primary key, but indicates the correct order of the data.

I need to write a query that joins these tables and returns the "top 100" rows, since a given id_pub value. I originally did this using a "where rownum <= 100" clause, but I found this could miss rows. So now I just have a plain "select <columns> from <tables> where <joins> and id_pub > xxx order by id_pub" statement. I open this as a java resultset, and then just loop until I've plucked off 100 records.

Now I'm guessing this isn't very efficient. I'm hoping that jdbc / oracle has some kind of buffering so that it is not loading the entire (up to) 350,000 rows into the resultset in order for me to get just that 100. What I do know it's doing is using TEMP space to do the sort, because when I run the SQL with a low id_pub value, we're getting 'out of TEMP space' errors.

I would appreciate any advise on:

• How inefficient is it to open a large resultset like this in order to just get the first 100 records - is jdbc / oracle as clever as I'm hoping?
• How much would putting an index on the id_pub column help? I don't know a great deal about indexes, but does an index mean that oracle would no longer need TEMP space to sort the results of a query 'ordered by' an indexed column?
• Is there a better way of getting an ordered chunk of 100 records out of the middle of about 350,000 records?

Thanks very much for any advise,

Rich Received on Tue Dec 18 2001 - 06:39:34 CST