TECHNIQUES FOR PAGINATION IN SQL / Solutions - execution plans - block reads

Quick links

Parameter values

P_CATEGORY    = 'GGGGGGGGGGGGGGGGGGGG'
P_DIRECTION   = 'ASC'
P_PAGE_SIZE   = 10
P_PAGE_NUMBER = 100
P_PAGE_START  = 100
P_PAGE_STOP   = 100
P_FIRST_ROW   = 991
P_LAST_ROW    = 1000

Results

Oracle SQL Developer's autotrace can be configured to display less/more information from the V$SQL_PLAN_STATISTICS_ALL view in Tools / Preferences menu selecting Database: Autotrace parameters option.

Description and SQL Oracle SQL Developer - Autotrace screenshot
using the V$SQL_PLAN_STATISTICS_ALL view
Standard techniques  1. - 4.

1. Full sort / no sort, fetch until last row

Fetch is stopped by the client after last row is retrieved.

SELECT ROWNUM AS rn
     , t.*
  FROM (SELECT t.*
          FROM test_table t
         WHERE category_col = :P_CATEGORY
         ORDER BY order_col
       ) t
;

This case cannot be autotraced because of the client activity.

  • with category-only index: 4890 logical block reads.
  • with concatenated index: 2002 logical block reads.

Look at template.sql to see how this was measured using the V$MYSTAT view.

2. Full sort / numbering, fetch page rows only

SELECT t.* 
  FROM (
        SELECT ROWNUM AS rn
             , t.* 
          FROM (
                SELECT t.* 
                  FROM test_table t
                 WHERE category_col = :P_CATEGORY
                ORDER BY order_col
                ) t
       ) t
 WHERE rn BETWEEN :P_FIRST_ROW AND :P_LAST_ROW
;

5038 logical block reads

3. Top-N query, extended extremal values search

SELECT t.* 
  FROM (
        SELECT ROWNUM AS rn
             , t.* 
          FROM (
                SELECT t.* 
                  FROM test_table t
                 WHERE category_col = :P_CATEGORY
                ORDER BY order_col
                ) t
         WHERE ROWNUM <= :P_LAST_ROW
       ) t
 WHERE rn >= :P_FIRST_ROW
;

1006 logical block reads

4. Numbering with analytical functions, fetch relevant rows only,
- another Top-N query form

SELECT t.* 
  FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY order_col) AS rn
             , t.* 
          FROM test_table t
         WHERE category_col = 'GGGGGGGGGGGGGGGGGGGG'
       ) t
 WHERE rn BETWEEN 991 AND 1000
;

1008 logical block reads

4/VAR. - Numbering with analytical functions, fetch relevant rows only,
- "PARTITION BY"-clause included -> NOT Top-N

Replace the OVER-clause of the analytical function expression in the above query with one of the following:
  1. ... OVER (PARTITION BY category_col ORDER BY order_col)
  2. ... OVER (PARTITION BY 'x' ORDER BY order_col)

5038 logical block reads

Optimal techniques using pure SQL  5. - 6.

5. ROWID index lookup with self join, real "Top-N - Last-M" query

Note: see the article on ROWID issue with IOTs

SELECT i.rn, t.* 
  FROM (SELECT i.*
          FROM (SELECT i.*, ROWNUM AS rn 
                  FROM (
                        SELECT ROWID AS a_rowid
                          FROM test_table t
                         WHERE category_col = :P_CATEGORY
                        ORDER BY order_col
                       ) i
                 WHERE ROWNUM <= :P_LAST_ROW
               ) i
         WHERE rn >= :P_FIRST_ROW
       ) i
     , test_table t
 WHERE i.a_rowid = t.ROWID
 ORDER BY rn
;

16 logical block reads

6. Index based first row value lookup with one page fetch forward

SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn, t.*
  FROM (SELECT t.* 
          FROM test_table t
         WHERE category_col = :P_CATEGORY
           AND order_col >= 
                (SELECT order_col
                   FROM (SELECT order_col, ROWNUM rn
                           FROM (SELECT order_col 
                                   FROM test_table t 
                                  WHERE category_col = :P_CATEGORY
                                 ORDER BY order_col
                                 ) 
                          WHERE ROWNUM <= :P_FIRST_ROW
                         )
                   WHERE rn = :P_FIRST_ROW
                ) -- first item on page
         ORDER BY order_col
       ) t
 WHERE ROWNUM <= :P_PAGE_SIZE
;

19 logical block reads

Better techniques using ORDBMS extensions  7. - 9.

7. Using a custom domain index

  1. Using an operator - category_page_between - bound to the domain index:

    SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn
         , t.* 
      FROM test_table t
     WHERE category_page('category_col, order_col'
                       , :P_CATEGORY, :P_DIRECTION
                       , :P_PAGE_SIZE, :P_PAGE_NUMBER) > 0
    ;

  2. Using another operator - category_page_between - bound to the domain index:

    SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn
         , t.* 
      FROM test_table t
     WHERE category_page_between('category_col, order_col'
                               , :P_CATEGORY, :P_PAGE_SIZE
                               , :P_PAGE_START, :P_PAGE_STOP) > 0
    ;


See operator definitions, helper packaged functions and/or domain index type specification on actual operators, parameters and bindings.

a. and b. are very similarly implemented and they are executed in the same way as it can be seen on the execution plans.

16 logical block reads

a.   operator - category_page

b.   operator - category_page_between

8.a) Using a table function that returns row numbers and ROWIDs from any table

SELECT i.rn
     , t.* 
  FROM TABLE(category_page_tabfunc_pkg.get_table_page
                 ( 'test_table', 'category_col', :P_CATEGORY
                 , 'order_col', :P_DIRECTION, :P_PAGE_SIZE
                 , :P_PAGE_START, :P_PAGE_STOP)
       ) i
     , test_table t
 WHERE i.a_rowid = t.ROWID
 ORDER BY rn
;

16 logical block reads

8.b) Using a table function that returns entire rows of a specific table

SELECT ROWNUM + :P_FIRST_ROW - 1 AS rn, t.*
 FROM (SELECT *
        FROM TABLE(get_paged__test_table
                       ( 'category_col', :P_CATEGORY
                       , 'order_col', :P_DIRECTION, :P_PAGE_SIZE
                       , :P_PAGE_START, :P_PAGE_STOP)
             ) t
       ORDER BY order_col
     ) t
;

16 logical block reads

9. Cursor functions

The execution plan and autotrace output is displayed for the following query:

SELECT
       category_page_tabfunc_pkg.get_table_page_cursor
           ( 'test_table', '*', 'category_col', :P_CATEGORY
           , 'order_col', :P_DIRECTION, :P_PAGE_SIZE
           , :P_PAGE_START, :P_PAGE_STOP)
         AS result_set
  FROM dual
;

Note:

The function could be executed directly also.

• This is how to call the function in SQL*Plus using a context variable and PL/SQL:

VARIABLE c REFCURSOR;
BEGIN
  :c := category_page_tabfunc_pkg.get_table_page_cursor
            ( 'test_table', '*', 'category_col', :P_CATEGORY
            , 'order_col', :P_DIRECTION, :P_PAGE_SIZE
            , :P_PAGE_START, :P_PAGE_STOP)
  ;
END;
/
PRINT c;

• This is how to call the function from Java, a 3GL language:

  ...
  CallableStatement call = connection.prepareCall(
    "{ ? = call category_page_tabfunc_pkg.get_table_page_cursor("
     + "'test_table', '<select list>', 'category_col'"
     + ", ?, 'order_col', ?, ?, ?, ?) }");
  call.registerOutParameter(1, OracleTypes.CURSOR);
  call.setString(2, pCategory);
  call.setInt(3, pDirection);
  call.setInt(4, pPageSize);
  call.setInt(5, pPageStart);
  call.setInt(6, pPageStop);
  stmt.execute();
  ResultSet rs = (ResultSet)stmt.getObject(1);
  ...
  rs.close();
  ...
    

16 logical block reads



Note:

The function call is not seen, but the value of the consistent gets statistic testifies that the function has been called.
However I found no way to get this information show up on the autotrace output or on an execution plan as well.
Although I tried nesting the query as a non-merged view and turn on the display of the Projection information. This resulted in ORA-600 internal error, because of having a REFCURSOR type in the subquery.

Back to main page.