The specified amount of InitialLONGFetchSize characters or bytes for LONG or LONG RAW column data is retrieved into the cache during the Read method invocations on the OracleDataReader object.

By default, InitialLONGFetchSize is set to 0. In this case, ODP.NET does not fetch any LONG or LONG RAW column data during the Read method invocations on the OracleDataReader object. The LONG or LONG RAW data is fetched when the typed accessor method is explicitly invoked for the LONG or LONG RAW column, which incurs a database round-trip because no data is cached.

If InitialLONGFetchSize is set to a value greater than 0, that amount of specified data is cached by ODP.NET during the Read method invocations on the OracleDataReader object. If the application requests an amount of data less than or equal to the InitialLONGFetchSize through the typed accessor methods, no database round-trip is incurred. However, an additional database round-trip is required to fetch data beyond InitialLONGFetchSize.

To obtain data beyond the InitialLONGFetchSize characters or bytes, one of the following must be in the select list:

• Primary key

• ROWID

• Unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it)

To be able to fetch the entire LONG or LONG RAW data without having a primary key column, a ROWID, or unique columns in the select list, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the number of characters or bytes needed to be retrieved.

The LONG or LONG RAW data is returned when the appropriate typed accessor method (GetChars, GetOracleString, or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.

By setting InitialLONGFetchSize to -1, it is possible to fetch the entire LONG or LONG RAW data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list.

When InitialLONGFetchSize is set to -1, the entire LONG or LONG RAW data is retrieved and cached during Read method invocations on the OracleDataReader object. Calls to GetString, GetOracleString, GetChars, GetBytes, or GetOracleBinary in the OracleDataReader return the entire column data.

By default, the InitialLOBFetchSize property is 0. This value dictates to ODP.NET that any LOBs selected will have their client LOB data fetches deferred until after the OracleDataReader Read, such as when using the an accessor. Each LOB value is retrieved only at the point it is individually accessed.

The advantage of using this retrieval strategy is that it conserves client memory and bandwidth. If the LOBs selected are either very large or not necessary to be immediately consumed by the end user, or both, then the application can perform better if LOBs are retrieved as needed, rather than all at once.

If InitialLOBFetchSize is set to a value greater than 0, ODP.NET caches LOB data up to InitialLOBFetchSize characters or bytes for each LOB selected during the Read method invocations on the OracleDataReader object. The maximum value is 2,147,483,647 (2GB). If the total size of a selected LOB is less than this number, the entire LOB data will be read.

By pre-fetching all LOB entries in one or more database round trips, applications can perform faster by reducing round trips. This approach is most advantageous when most LOBs are either small in size, or consumed by the end user almost immediately, or both. The down side of a large fetch size is higher memory consumption.

This section discusses the ways to fetch beyond the InitialLOBFetchSize characters or bytes that are cached.

The remaining LOB data is returned when a typed accessor is invoked, regardless of the value set to the InitialLOBFetchSize property. Primary key, ROWID, or unique columns are not required to be in the query select list to obtain data beyond the specified InitialLOBFetchSize.

The GetOracleBlob, GetOracleClob, GetOracleBlobForUpdate, and GetOracleClobForUpdate methods can now be invoked even if InitialLOBFetchSize is greater than 0.

To fetch all LOB data selected during the read operation and not be bound by a set limit per LOB, set InitialLOBFetchSize to -1. A new default behavior has been introduced for ODP.NET Release 12.1.0.2 and higher when InitialLobFetchSize is set to -1.

When LegacyEntireLOBFetch = 0, which is the default value, the following operations are invoked for a LOB column:

• OracleDataReader.GetOracleClob(): returns OracleClob object

• OracleDataReader.GetOracleBlob() : returns OracleBlob object

• OracleDataReader.GetOracleClobForUpdate(): returns OracleClob object

• OracleDataReader.GetOracleBlobForUpdate(): returns OracleBlob object

• OracleDataReader.GetOracleValue(): returns OracleClob object for a CLOB column

• OracleDataReader.GetOracleValue(): returns OracleBlob object for a BLOB column

• OracleDataAdapter.Fill() with ProviderSpecificTypes=true: populates DataTable with OracleClob for a CLOB column

• OracleDataAdapter.Fill() with ProviderSpecificTypes=true: populates DataTable with OracleBlob for a BLOB column

To use the old behavior, set LegacyEntireLobFetch = 1 in the ODP.NET configuration.

When LegacyEntireLobFetch = 1 and InitialLOBFetchSize = -1, GetOracleClob, GetOracleClobForUpdate, GetOracleBlob, and GetOracleBlobForUpdate methods are not supported. The following operations are invoked for a LOB column in this scenario:

• OracleDataReader.GetOracleClob(): throws InvalidCastException()

• OracleDataReader.GetOracleBlob(): throws InvalidCastException()

• OracleDataReader.GetOracleClobForUpdate(): throws InvalidCastException()

• OracleDataReader.GetOracleBlobForUpdate(): throws InvalidCastException()

• OracleDataReader.GetOracleValue(): returns OracleString object for a CLOB column

• OracleDataReader.GetOracleValue(): returns OracleBinary object for a BLOB column

• OracleDataAdapter.Fill() with ProviderSpecificTypes=true: populates DataTable with OracleString for a CLOB column

• OracleDataAdapter.Fill() with ProviderSpecificTypes=true: populates DataTable with OracleBinary for a BLOB column

For releases prior to ODP.NET 12.1.0.2, by setting InitialLOBFetchSize to -1, it is possible to fetch the entire LOB data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list. When InitialLOBFetchSize is set to -1, the entire LOB column data is fetched and cached during the Read method invocations on the OracleDataReader object. Calls to GetString, GetOracleString, GetChars, GetBytes, or GetOracleBinary in the OracleDataReader allow retrieving all data.

This section discusses the advantages and disadvantages of the various InitialLOBFetchSize property settings in different situations.

An application does not have to choose between performance and OracleBlob and OracleClob functionality. Setting the InitialLOBFetchSize property results in a performance boost and still gives the flexibility to use the OracleBlob and OracleClob objects.

If the size of the LOB data is unknown or if the LOB data size varies irregularly, then it is better to leave the InitialLOBFetchSize property to its default value of 0. This still gives better performance in most cases.

Setting the InitialLOBFetchSize property to a size equal to or greater than the LOB data size for most rows improves performance. It is generally recommended that the InitialLOBFetchSize property be set to a value larger than the size of the LOB data for more than 80% of the rows returned by the query. For example, if the size of the LOB data is less than 1 KB in 80% of the rows, and more than 1 MB for 20% of the rows, set the InitialLOBFetchSize property to 1 KB.

The FetchSize property represents the total memory size in bytes that ODP.NET allocates to cache the data fetched from a database round-trip.

The FetchSize property can be set on the OracleCommand, OracleDataReader, or OracleRefCursor object, depending on the situation. It controls the fetch size for filling a DataSet or DataTable using an OracleDataAdapter.

If the FetchSize property is set on the OracleCommand object, then the newly created OracleDataReader object inherits the FetchSize property of the OracleCommand object. This inherited FetchSize value can be left as is, or modified to override the inherited value. The FetchSize property of the OracleDataReader object can be changed before the first Read method invocation, which allocates memory specified by the FetchSize property. All subsequent fetches from the database use the same cache allocated for that OracleDataReader object. Therefore, changing the FetchSize value after the first Read method invocation has no effect.

By fine-tuning the FetchSize property, applications can control memory usage and the number of rows fetched in one database round-trip for better performance.

For example, if a query returns 100 rows and each row takes 1024 bytes, then setting the FetchSize property to 102400 takes just one database round-trip to fetch 100 rows. For the same query, if the FetchSize property is set to 10240, it takes 10 database round-trips to retrieve 100 rows. If the application requires all the rows to be fetched from the result set, the first scenario is faster than the second. However, if the application requires just the first 10 rows from the result set, the second scenario can perform better because it fetches only 10 rows, not 100 rows. When the next 10 rows are fetched, then the memory allocated for rows 1-10 is reused for rows 11-20.

The larger the FetchSize, the more system memory is used. Developers should not set large fetch sizes if their client systems have limited memory resources.

The RowSize property of the OracleCommand or OracleRefCursor object is populated with the row size (in bytes) after an execution of a SELECT statement. The FetchSize property can then be set to a value relative to the RowSize property by setting it to the result of multiplying the RowSize value times the number of rows to fetch for each database round-trip.

For example, setting the FetchSize to RowSize * 10 forces the OracleDataReader object to fetch exactly 10 rows for each database round-trip. Note that the RowSize value does not change due to the data length in each individual column. Instead, the RowSize value is determined strictly from the metadata information of the database table(s) that the SELECT statement is executed against.

The RowSize property can be used to set the FetchSize property at design time or at run time, as described in the following sections.