How to paginate through an ordered result set
February 9th, 2006 By Eddie Awad
You want to paginate through an ordered result set, returning a few rows at a time. For example:
HR@XE> select first_name, 2 hire_date 3 from employees 4 order by hire_date 5 / FIRST_NAME HIRE_DATE -------------------- --------- Steven 17-JUN-87 Jennifer 17-SEP-87 Neena 21-SEP-89 Alexander 03-JAN-90 Bruce 21-MAY-91 Lex 13-JAN-93 Susan 07-JUN-94 Hermann 07-JUN-94 Shelley 07-JUN-94 William 07-JUN-94 Daniel 16-AUG-94 Nancy 17-AUG-94 Den 07-DEC-94 Payam 01-MAY-95
You want to return 3 rows at a time, while at the same time preserving the order of the result set.
One way (using rownum):
HR@XE> var lower number HR@XE> var higher number HR@XE> exec :lower := 1 PL/SQL procedure successfully completed. HR@XE> exec :higher := 3 PL/SQL procedure successfully completed. HR@XE> select first_name, 2 hire_date 3 from 4 (select a.*, 5 rownum r 6 from 7 (select first_name, 8 hire_date 9 from employees 10 order by hire_date) 11 a 12 where rownum <= :higher) 13 where r >= :lower 14 / FIRST_NAME HIRE_DATE -------------------- --------- Steven 17-JUN-87 Jennifer 17-SEP-87 Neena 21-SEP-89 HR@XE> exec :lower := 4 PL/SQL procedure successfully completed. HR@XE> exec :higher := 6 PL/SQL procedure successfully completed. HR@XE> / FIRST_NAME HIRE_DATE -------------------- --------- Alexander 03-JAN-90 Bruce 21-MAY-91 Lex 13-JAN-93
Another way (using row_number):
HR@XE> exec :lower := 1 PL/SQL procedure successfully completed. HR@XE> exec :higher := 3 PL/SQL procedure successfully completed. HR@XE> select first_name, 2 hire_date 3 from 4 (select row_number() over( 5 order by hire_date) as 6 rn, 7 first_name, 8 hire_date 9 from employees) 10 x 11 where rn between :lower 12 and :higher 13 / FIRST_NAME HIRE_DATE -------------------- --------- Steven 17-JUN-87 Jennifer 17-SEP-87 Neena 21-SEP-89 HR@XE> exec :lower := 4 PL/SQL procedure successfully completed. HR@XE> exec :higher := 6 PL/SQL procedure successfully completed. HR@XE> / FIRST_NAME HIRE_DATE -------------------- --------- Alexander 03-JAN-90 Bruce 21-MAY-91 Lex 13-JAN-93
February 10th, 2006 at 2:49 am
Hi Eddie,
how about the execution plans – are they the same?
Greetings
February 10th, 2006 at 8:37 pm
Using rownum:
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 3638 | 4 (25)| 00:00:01 | |* 1 | VIEW | | 107 | 3638 | 4 (25)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 107 | 2247 | 4 (25)| 00:00:01 | |* 4 | SORT ORDER BY STOPKEY| | 107 | 1605 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("R">=TO_NUMBER(:LOWER)) 2 - filter(ROWNUM< =TO_NUMBER(:HIGHER)) 4 - filter(ROWNUM<=TO_NUMBER(:HIGHER)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 556 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3 rows processedUsing row_number:
---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 3638 | 4 (25)| 00:00:01 | |* 1 | VIEW | | 107 | 3638 | 4 (25)| 00:00:01 | | 2 | WINDOW SORT | | 107 | 1605 | 4 (25)| 00:00:01 | |* 3 | FILTER | | | | | | | 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=TO_NUMBER(:LOWER) AND "RN"< =TO_NUMBER(:HIGHER)) 3 - filter(TO_NUMBER(:LOWER)<=TO_NUMBER(:HIGHER)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 556 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3 rows processedLooks like the optimizer had to go through less steps using row_number.
February 11th, 2006 at 10:54 am
Thank you!
is see both variants uses the same resources.
and strange – on the second plan only 75% of CPU cost is shown;
where is the rest? But that’s not a ‘winning battle question’
Regards
Karl