The official way to achieve pagination in Oracle DB is not very elegant and very very slow. The reasons why it is slow are 2:
- The order by command must be executed before the fetching
- The fetching is really slow, because of complex functions on “select” part of the statement, which is executed in fetching time (not at “where” execution time).
So, the official way goes like this and all the job is done here before the separation into pages:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from (
/*
your_query_goes_here,
with order by
*/
with computers as (
select 1 pc_id, 'ZX Spectrum 48K' pc_desc, 48 ram from dual
union
select 2 pc_id, 'Amstrad CPC 464' pc_desc, 64 ram from dual
union
select 3 pc_id, 'Amstrad CPC 6128' pc_desc, 128 ram from dual
union
select 4 pc_id, 'Commodore 64' pc_desc, 64 ram from dual)
select pc_id, pc_desc, ram from computers
order by pc_desc
) a
where ROWNUM <= 100 )
where rnum >= 0;
This query is really slow for many rows and complex select statements.
In case we want to achieve good performance, we can execute the pagination in 2 steps.
In the first step we select our ordered results and in the second step we do the pagination.
In the first step we get also the record count. So, in application level, this record count will help to
decide our pagination needs. For example a java application can call this first function to get the results
and then to calculate the pagination and call the second step.
This will be demonstrated in PL/SQL side in the next anonymous block of code:
First create the data types:
create type computer_record as object (pc_id number, pc_desc varchar2(100));
create type computer_table as table of computer_record;
Then run the anonymous block:
declare
l_pc_id number;
l_pc_desc varchar2(100);
l_ram number;
l_search_results computer_table;
l_results_count number;
l_final_recordset sys_refcursor;
procedure find_PC (search_string_i in varchar2,
search_results_o out computer_table,
search_results_count_o out number) is
l_pc_id number;
l_pc_desc varchar2(100);
l_ram number;
found_computers computer_table;
found_computers_rec computer_record;
computer_search_results sys_refcursor;
begin
open computer_search_results for
with computers as (
select 1 pc_id, 'ZX Spectrum 48K' pc_desc, 48 ram from dual
union
select 2 pc_id, 'Amstrad CPC 464' pc_desc, 64 ram from dual
union
select 3 pc_id, 'Amstrad CPC 6128' pc_desc, 128 ram from dual
union
select 4 pc_id, 'Commodore 64' pc_desc, 64 ram from dual)
select pc_id, pc_desc from computers
where 1=1
and pc_desc like '%'|| search_string_i ||'%';
found_computers := new computer_table();
loop
fetch computer_search_results into l_pc_id, l_pc_desc;
exit when computer_search_results%notfound;
found_computers_rec := new computer_record(l_pc_id, l_pc_desc);
found_computers.extend(1);
found_computers(computer_search_results%rowcount) := found_computers_rec;
end loop;
search_results_o := found_computers;
search_results_count_o := computer_search_results%rowcount;
end find_PC;
procedure final_results (search_results_i in computer_table,
row_start_i in number,
row_end_i in number,
final_recordset_o out sys_refcursor) is
begin
open final_recordset_o for
with computers as (
select 1 pc_id, 'ZX Spectrum 48K' pc_desc, 48 ram from dual
union
select 2 pc_id, 'Amstrad CPC 464' pc_desc, 64 ram from dual
union
select 3 pc_id, 'Amstrad CPC 6128' pc_desc, 128 ram from dual
union
select 4 pc_id, 'Commodore 64' pc_desc, 64 ram from dual)
select c.pc_id, c.pc_desc, c.ram
--from table(cast(search_project as found_projects)) fp, proj
from table(search_results_i) sr,
computers c
where 1=1
and sr.pc_id=c.pc_id
and rownum between row_start_i and row_end_i
order by sr.pc_id;
end final_results;
begin
--enter search string and get the results in data table
find_PC(search_string_i => 'Amstrad',
search_results_o => l_search_results /* computer_table */,
search_results_count_o => l_results_count);
--display the recordcount
dbms_output.put_line('Computers found: ' || l_results_count);
--feed the data table into this function
--in order to join the results with the original table
--and order by data table's ID
final_results(search_results_i => l_search_results,
row_start_i => 0,
row_end_i => 5,
final_recordset_o => l_final_recordset);
loop
fetch l_final_recordset into l_pc_id, l_pc_desc, l_ram;
exit when l_final_recordset%notfound;
dbms_output.put_line('FOUND: Computer ID: ' || l_pc_id ||
', Description: ' || l_pc_desc ||
', RAM: ' || l_ram);
end loop;
end;
Please, note the following:
– We don’t pass Sys_RefCursor from one function to the other, because cursors are one-use only and forward only.
This means that since we read the cursor in order to take the record count we can not reuse it.
This is the reason why we use the data tables.
– However, the use of data tables is a bit tricky, because they must be declared in the schema, before we use them
in SQL query. This is the reason why we must create them as schema objects. It is not enough to declare them
in procedure level; this does not work in Oracle.
– Also tricky is the part that in an anonymous block the procedures must be declared after all variables, otherwise we get compilation error