Sunday, May 29, 2011

Pagination in Data View : How to Facilitate My SQL Limit in Oracle

The solution provided here probably is not new to many. I try to combine the optimum solution that I know in one post.

Those who are acquainted with My SQL, surely knows about LIMIT keyword in My SQL. Oracle does not have LIMIT keyword feature. But you can make the pagination of Data View better using two feature of Oracle - ROWNUM and REF CURSOR.

create or replace procedure paging(p_pagenum IN number,p_pagesize IN number, p_cursor OUT SYS_REFCURSOR)is
begin
open p_cursor FOR
SELECT DATA
FROM
(SELECT ROWNUM RNUM,a.*
FROM
(
SELECT
object_name data
FROM
TBL_SOURCE
ORDER BY created
) A
WHERE ROWNUM<=p_pagesize*p_pagenum )
WHERE RNUM > (p_pagesize*p_pagenum) - p_pagesize
ORDER BY RNUM;
end paging;

This procedure will be sufficient for pagination. It is somewhat complex due to the fact that ROWNUM is a pseudo column. From the select statement it is clear that the first page data loading will be the fastest and the last page data loading will be the slowest. This is because, for the last page, you have to fetch all rows.

Next comes the REF_CURSOR. It is a handler of the source data like other cursors. In stead of fetching all data, it fetches data one by one and provides streaming. But unlike other cursor this cursors can be consumed by other pl/sql or high level languages like C# or java.

See also - http://www.oradev.com/ref_cursor.jsp and http://support.microsoft.com/kb/322160

If you only use ROWNUM and do not use REF_CURSOR, you will still get better performance. In that case, you have to write inline SQL in java or C# code.