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?

Mark Clancy
  • 7,831
  • 8
  • 43
  • 49

2 Answers2

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
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 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. – Mark Clancy Sep 29 '17 at 08:57
  • 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. – Mark Clancy Sep 29 '17 at 09:03
  • @markvpc Its probably the difference between doing a [full table scan and an index scan](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9530497800346503938) - 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. – MT0 Sep 29 '17 at 09:17
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
ehuehu
  • 1
  • 1