Database Reference
In-Depth Information
OCI
With OCI, row prefetching is controlled by two statement attributes: OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_
PREFETCH_MEMORY . The former limits the number of fetched rows. The latter limits the amount of memory (in bytes)
used to fetch the rows. The following code snippet shows how to call the OCIAttrSet function to set these attributes.
The C program in the row_prefetching.c file provides a complete example:
ub4 rows = 100;
OCIAttrSet(stm, // statement handle
OCI_HTYPE_STMT, // type of handle being modified
&rows, // attribute.s value
sizeof(rows), // size of the attribute.s value
OCI_ATTR_PREFETCH_ROWS , // attribute being set
err); // error handle
ub4 memory = 10240;
OCIAttrSet(stm, // statement handle
OCI_HTYPE_STMT, // type of handle being modified
&memory, // attribute.s value
sizeof(memory), // size of the attribute.s value
OCI_ATTR_PREFETCH_MEMORY , // attribute being set
err); // error handle
When both attributes are set, the limit that is reached first is honored. To switch off row prefetching, you must set
both attributes to zero.
JDBC
Row prefetching is enabled with the Oracle JDBC driver by default. You can change the default number of fetched
rows (10) in two ways. The first is to specify a property when opening a connection to the database engine with either
the OracleDataSource or the OracleDriver class. The following code snippet shows an example where the user, the
password, and the number of prefetched rows are set for an OracleDataSource object. Note that in this case, because
it's set to 1, row prefetching is disabled:
connectionProperties = new Properties();
connectionProperties.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, user);
connectionProperties.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, password);
connectionProperties.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "1");
dataSource.setConnectionProperties(connectionProperties);
The second way is to override the default value at the connection level by using the setFetchSize method of
either the java.sql.Statement or java.sql.ResultSet interface (and hence, their subinterfaces). The following code
snippet shows an example where the setFetchSize method is used to set the number of fetched rows to 100. The Java
program in the RowPrefetching.java file provides a complete example:
sql = "SELECT id, pad FROM t";
statement = connection.prepareStatement(sql);
statement.setFetchSize(100);
resultset = statement.executeQuery();
 
Search WWH ::




Custom Search