Devildib
asked on
oracle pagination query performance issue
The following query for paginating say 2000 records is having performace issues.
for initial pages its fine.As the page number passed increases, time for execution increases.
I need some way to skip already traveres records or some alternative way to query reults speedily.
Say i am showing 20 records per page in total of 50 pages
and i want records from 980 to 1000.
How can i spped this query up?
SELECT * FROM
(
SELECT b.*, rownum r
FROM
(
SELECT AA,BB,CC,DD
FROM table tab WHERE AA= 'testdata1'
AND B = 'testdata2'
AND C >= 'date1' AND B < 'date2' -- C is date column
AND D BETWEEN 1 AND 1000000 -- amount column
ORDER BY C DESC -- Date column C is indexed
) b
WHERE rownum < ((Pagenum * Pagesize) + 1 )
)
WHERE r >= (((Pagenum-1) * Pagesize) + 1)
for initial pages its fine.As the page number passed increases, time for execution increases.
I need some way to skip already traveres records or some alternative way to query reults speedily.
Say i am showing 20 records per page in total of 50 pages
and i want records from 980 to 1000.
How can i spped this query up?
SELECT * FROM
(
SELECT b.*, rownum r
FROM
(
SELECT AA,BB,CC,DD
FROM table tab WHERE AA= 'testdata1'
AND B = 'testdata2'
AND C >= 'date1' AND B < 'date2' -- C is date column
AND D BETWEEN 1 AND 1000000 -- amount column
ORDER BY C DESC -- Date column C is indexed
) b
WHERE rownum < ((Pagenum * Pagesize) + 1 )
)
WHERE r >= (((Pagenum-1) * Pagesize) + 1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
upgrade to oracle 12
a new feature of oracle 12 is an extended clause for limiting the rows
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55636
or use an advanced grid component which has the pagination built in