Use rownum instead of scrollable resultsets
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.