Prefetch properties

Use the Prefetch row count and Prefetch buffer size properties to enable prefetching for SELECT statements. If row prefetching is enabled, the connector fetches the number of rows that is specified by the Array size property. In addition, the Oracle client fetches a number of rows that is based on the values of the Prefetch row count and Prefetch buffer size properties.

Usage

You can set the Prefetch row count property, the Prefetch buffer size property, or set both properties. If you set both properties to a value that is greater than 0, the Oracle client tries to prefetch the number of rows that is specified for the Prefetch row count property. If the number of rows cannot fit in the memory size that is specified for the Prefetch buffer size property, the Oracle client prefetches as many rows as can fit into the buffer.

When you set the Prefetch row count or Prefetch buffer size property to 0, the type of row prefetching that is controlled by that property is disabled.

The Oracle client immediately provides the rows that are fetched based on the value of the Array size property to the connector. The Oracle client caches the rows that are fetched based on the values of the Prefetch row count and Prefetch buffer size properties. As the connector continues to request data for the currently running SELECT statement, the fetch requests are optimized because the prefetched rows are cached.

The following table shows the values that you can set these properties to.

Table 1. Values for the prefetch row count and prefetch buffer size properties
Property	Available values	Default
Prefetch row count	0 - 999999999	1
Prefetch buffer size (KB)	0 - 100240	0 By default, row prefetching based on buffer size is disabled.