Geek Answers Handbook

Oracle & Pagination

I have an oracle table with 99896618 records.

I need to get a small piece of data (say, 100 records) to show it on a web page (in the web world we call it a search call). I am currently using the following query to accomplish this, however users are not satisfied with the performance.

SELECT * FROM (select rownum rnum,f.* from findings f where rownum<90000100 ) WHERE rnum > 90000000

Currently, it takes 1 min 22 seconds to get the results. Is there any way to do this better. I am definitely open to any suggestions, including changing the structure of the table or adding indexes.

(Just FYI, I use ASP.NET as server-side web technology and ADO.NET as the level of data access and silverlight for client-side presentation)

+6
oracle silverlight
source share
4 answers

Your request will have to count the first 90M records to get the next 100 , so there is hardly a place for improvement.

I do not see the ORDER BY in your subquery, but you probably have one. In this case, you can create an index on it.

And the question is: do your users really click on 900K pages before complaining about performance?

Update:

If you need the last page, you need to rewrite the ORDER BY column in descending order:

 SELECT * FROM ( SELECT rownum rnum, f.* FROM findings f ORDER BY record_ordering_column DESC ) WHERE rnum > 900 AND rownum <= 100

and create an index on record_ordering_column

Note that I mix rownum with nested queries to improve performance.

See this blog post for more details:

+6
source share

From one of your comments:

most of the time (about 95% of the time) users are interested in the latest (last) entries

In this case, why not show the entries in the reverse order, so that in 95% of cases, users are interested in page 1 and not on page 900,000?

If they really want to see the "900,000" page, it means that they have long been interested in data, so they allow them to filter the data, for example. date range. Just paging through 100 million lines without any filtering will never be done.

+5
source share

If you want to modify the table, I would suggest adding a rownumber column to the table (using the insert trigger and sequence to set it), and then adding an index to this column.

+1
source share

Do you really need to return the entire string? Because this means that you are not using any indexes.

If you still need to get the whole line. Use the following template:

 SELECT * FROM findings f1 WHERE f1.rowid IN (SELECT rownum rnum, row_id FROM ( SELECT f.rowid row_id FROM findings f ORDER BY record_ordering_column ) WHERE rownum > 900 ) WHERE rnum <= 100;

See AskTom

Note: a subtle optional SELECT clause, as well as using a ROWID query.

If you add an index to record_ordering_column, then paganation will use the index to get the ROWID set. Then load only the blocks containing the rows identified by their ROWID.

This will be better than your current query, which will be a full table scan.

0
source share

More articles:


All Articles