HELP ! P R E F E T C H -- Trick to using prefetch in JDBC with Oracle

From: Peter B. Nelson <PBNelson_at_Black-Hole.com>
Date: 26 May 1999 10:19:09 -0500
Message-ID: <374c10ad.97334820@news1.newscene.com>

Has anyone seen this weirdness? (as this message was cross-posted to the world, please cc any reply to PBNelson_at_Black-Hole.com - thanks :-)

...so, I need to increase the prefetch queue size.

Is there a trick to using the OracleStatement.defaultRowPrefetch option? Here's what I tried:

THE STATEMENT:

---

  OracleCallableStatement.setRowPrefetch(100)

should set my prefetch queue, from its default of 10, to 100. However, judging from performance, the prefetch queue size doesn't change. Even though the statement:

---

  OracleCallableStatement.getRowPrefetch()

returns 100, my SQL retrieves are abysmally slow whenever the result set exceeds 10, which is the default size.

Here is an example of my select statement:

---

  CallableStatement stmt = this.conn.prepareCall("select * from party where rownum < 25");
  stmt.executeQuery();

Is there something else I must do to make the prefetch queue bigger/faster? Do I need to switch a boolean, or something?

P.S. I'm using jBuilder 2 with Oracle 8 JDBC drivers v1.11, downloaded 5/25/99. Received on Wed May 26 1999 - 10:19:09 CDT