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.