0

I'm using Oracle 11.2 and am trying to write a paging query on a table with millions of rows in it. Reading other articles I think I've found the best approach but it slows down the higher the page number is.

This is my solution. First I get a list of primary key values (ID) for that page of data. I then get all the other table data matching those ids. However this still runs slowly. 

SELECT * FROM mytable WHERE ID IN ( SELECT ID FROM ( SELECT ID, ROWNUM rnum FROM ( SELECT ID FROM mytable ORDER BY ID ) results WHERE ROWNUM <= 1000010 ) WHERE rnum >= 1000001 )

Execution Time: 30+ seconds.

If I do the inner query separately and manually pass the ids to the outer query it's much faster:

SELECT ID FROM ( SELECT ID, ROWNUM rnum FROM ( SELECT ID FROM mytable ORDER BY ID ) results WHERE ROWNUM <= 1000010 ) WHERE rnum >= 1000001 Execution Time: 0.2 seconds. Results: 2134696, 2134697, 2134692, 2134693, 2134694, 2134695, 2134698, 2134699, 2134700, 2134701 SELECT * FROM mytable WHERE ID IN ( 2134696, 2134697, 2134692, 2134693, 2134694, 2134695, 2134698, 2134699, 2134700, 2134701 ) Execution Time: 0.03 seconds.

The first query should be as fast as the other 2 together but it's much slower.

Can anyone explain why this is and suggest a better solution?

Improve this question

2 Answers 2

Reset to default
2

You first query is doing two table (or index) scans (of millions of rows) and joining them together to filter the rows.

Your second and third queries are doing a single table (or index) scan each but they are not joining them together.

You should use something like:

SELECT * FROM ( SELECT r.*, ROWNUM rnum FROM ( SELECT * FROM mytable ORDER BY ID ) r WHERE ROWNUM <= 1000010 ) WHERE rnum >= 1000001

Which only does a single table scan.

In Oracle 12c you can use:

SELECT * FROM MYTABLE ORDER BY id OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the reply. I started off with your query but again it was taking 30+ seconds to return results. The fastest way currently for my solution is to get the list of ids first then fire a separate query to get the related data. Seems crazy to me but it works. I assume Oracle 12 handles this better.
Yes it's the primary key column and is very fast to fetch on it's own. The problem occurs when getting other data in that table.
@markvpc Its probably the difference between doing a full table scan and an index scan - you will need to look at the explain plans for the various queries to see the difference in what uses indexes and what doesn't and then look at why you might not be using the index.
0

Best way for get total records count and records.

change [YOURTABLENAME] change 2 and 100 ( 2 "PAGENO", 100 "PERPAGE" )

with XPARAMS as (select 2 "PAGENO", 100 "PERPAGE" from dual) ,P2 as (select (XPARAMS.PAGENO-1)*XPARAMS.PERPAGE "STARTNO", XPARAMS.PAGENO*XPARAMS.PERPAGE "ENDNO" from XPARAMS) ,D1 as (select * from [YOURTABLENAME] t order by ID) ,DCOUNT as (select count(*) "TOTALCOUNT" from D1) ,D2 as (select rownum "RN" , D1.* from D1 ) ,D3 as (select D2.* from D2,P2 where D2.RN between P2.STARTNO and p2.ENDNO ) select D3.RN, DCOUNT.TOTALCOUNT, XPARAMS.PAGENO, XPARAMS.PERPAGE, D3.* from D3,DCOUNT, XPARAMS
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.