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?