This post originated from an RSS feed registered with Java Buzz by Andrej Koelewijn. Original Post: Use rownum instead of scrollable resultsets Feed Title: Andrej Koelewijn Feed URL: http://feeds.feedburner.com/AndrejKoelewijn Feed Description: On Oracle, Java and OpenSource Latest Java Buzz Posts Latest Java Buzz Posts by Andrej Koelewijn Latest Posts From 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.