Optimizer Overrides
Page 2 of 6

bulkFetch

Function

The bulkFetch optimizer property overrides the default number of rows that Cloudscape fetches at a time when reading a conglomerate (table or index). When this property is set to 1, Cloudscape fetches rows one at a time. Setting this property to a greater number allows Cloudscape to fetch more than one row at a time and reduces system overhead.

Setting the bulkFetch property uses memory. Cloudscape constructs a temporary cache for storing fetched rows of a size specified in the property. This cache uses memory based on the sum of the columns referenced by the scan, not the size of the entire row in the target conglomerate. Cloudscape fills the cache with the number of qualifying rows, up to the number specified with the property. Cloudscape applies simple predicates (such as WHERE Region = 'Europe') before fetching. When the scan completes, the row cache is freed.

Setting the bulkFetch property to a large value (up to the number of rows in a typical page) enhances performance at the expense of the amount of memory needed for the row cache. There is no performance gain in setting the bulkFetch size larger than the number of qualifying rows from the target table. For example, if the query will return only 5 rows, there is no performance benefit in setting this property to 30.

The default bulk fetch size is determined by the cloudscape.language.bulkFetchDefault system property; the default value for that property is 16. That value is optimal for most situations. For more information, see cloudscape.language.bulkFetchDefault.

Syntax

bulkFetch=size

Default Value

16.

Minimum Value

1.

Maximum Value

java.lang.Integer.MAX_INTEGER.

Example

SELECT *
FROM FlightAvailability
PROPERTIES bulkFetch=256

Restrictions

Bulk fetch is not used in the following cases:

  • with a joinStrategy=hash clause, since the hash join strategy always causes the entire table to be fetched at once
  • with updatable cursors, since a positioned update or delete requires the current scan position to locate a single target row
  • with nonmaterialized subqueries
  • if the table in question is the inner table of an equijoin on a unique key

When Useful

Bulk fetch is best suited for situations in which many rows are returned.

For example, it is useful for table scans or index range scans:

SELECT *
FROM Flights
WHERE orig_airport > 'AAB'
AND orig_airport < 'XYZ'

SELECT *
FROM Flights

SELECT *
FROM Flights
WHERE segment_number = 1

Scope

Table optimizer-override property.