Andrej Koelewijn

There are some interesting threads
on AskTom discussing the best method to query a page of records (on Oracle), say records 20 to 30 of a query. Tom Kyte’s advice is to use rownum in the where clause to retrieve only the required rows.

In java there’s another way to query a page of records. You can use a scrollable resultset. You position the cursor on the first record required using absolute(), and then you loop through the number of records you need. Here’s an example.

stmt = 
    conn.prepareStatement(
       "select * from big_table order by object_name",
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
stmt.setFetchSize(pagesize);
rset = stmt.executeQuery();
rset.absolute(start);

The approach adviced by tom kyte is as follows:

stmt =
  conn.prepareStatement(
                        "select * "
                        + "from ( select q.* "
                        + "       ,      rownum rnum "
                        + "       from ( select * "
                        + "              from   big_table "
                        + "              order  by object_name "
                        + "            ) q "
                        + "       where rownum <= ? "
                        + "     ) "
                        + "where rnum >= ? ");
stmt.setInt(1, end);
stmt.setInt(2, start);
rset = stmt.executeQuery();

The first method is portable, the second is only useable on Oracle.
So, if both perform the same i would prefer to use the scrollable
resultset. This, however is not the case as can be demonstrated using
tkprof. I ran both statements 10 times, querying the first 10 records
with the scrollable resultset, the next with the rownum approach, then
again 10 with the scrollable resultset, etc. In total i queried 200
records, so both statements where created 10 times. Here’s the output
from tkprof:

select * 
from
 big_table order by object_name


call     count       cpu    elapsed       disk      query    current        rows
------- -----  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.01       0.00          0          0          0           0
Execute     20      0.00       0.00          0          0          0           0
Fetch      110     17.07     287.51     100445      30140       1040        1100
------ -----  -------- ---------- ---------- ---------- ----------  ----------
total      140     17.08     287.52     100445      30140       1040        1100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5  

Rows     Row Source Operation
-----  ---------------------------------------------------
     20  SORT ORDER BY 
 221004   TABLE ACCESS FULL BIG_TABLE 

*******************************************************************************

select * 
from
 ( select q.        ,      rownum rnum        from ( select *               
  from   big_table               order  by object_name             ) q        
  where rownum <= :1      ) where rnum >= :2 


call     count       cpu    elapsed       disk      query    current        rows
------ -----  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       20      6.11      18.76      30074      30140          0         100
------ -----  -------- ---------- ---------- ---------- ----------  ----------
total       40      6.11      18.76      30074      30140          0         100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5  

Rows     Row Source Operation
------  ---------------------------------------------------
     10  VIEW  
     20   COUNT STOPKEY 
     20    VIEW  
     20     SORT ORDER BY STOPKEY 
 221004      TABLE ACCESS FULL BIG_TABLE 

As you can see from the output of tkprof, the rownum approach is better
for performance. Half the number of executes, and a lot less fetches.
So if you are using an Oracle database, and you’re not going to use
anything else in the future, use the rownum method.