.NET Type Accessors

Table 3-3 lists all the Oracle native database types that ODP.NET supports and the corresponding .NET Type that best represents the Oracle native type. The third column indicates the valid typed accessor that can be invoked for an Oracle native type to be obtained as a .NET type. If an invalid typed accessor is used for a column, an InvalidCastException is thrown. Oracle native datatypes depend on the version of the database; therefore, some datatypes are not available in earlier versions of Oracle Database.

ODP.NET Type Accessors

ODP.NET exposes provider-specific types that natively represent the datatypes in the database. In some cases, these ODP.NET types provide better performance and functionality that is not available to the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader by calling their respective typed accessor.

Table 3-4 lists the valid type accessors that ODP.NET uses to obtain ODP.NET Types for an Oracle native type.

Methods Supported or Not Supported for InitialLOBFetchSize

Table 3-5 and Table 3-6 list supported and not supported methods for the CLOB and BLOB datatypes when the OracleCommand InitialLOBFetchSize property is set to a nonzero value.

LOB Data Fetching Considerations

ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LOB data can be fetched in a random fashion.

To obtain data beyond InitialLOBFetchSize bytes or characters, 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)

The requested data is fetched from the database when the appropriate typed accessor method is called on the OracleDataReader object. Note that the primary key column is not required if InitialLOBFetchSize is set to 0.

In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLOBFetchSize property on the OracleCommand object to an amount equal to or greater than the bytes or characters that need to be retrieved.

Performance

Setting InitialLOBFetchSize to a nonzero value can improve performance in certain cases. Using InitialLOBFetchSize can provide better performance than retrieving the underlying LOB data using OracleBlob or OracleClob objects. This is true if an application does not need to obtain OracleBlob and OracleClob objects from the OracleDataReader and the size the LOB column data is not very large. InitialLOBFetchSize is particularly useful in cases where the size of the LOB column data returned by query is approximately the same for all the rows.

It is generally recommended that InitialLOBFetchSize 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 InitialLOBFetchSize to 1 KB.

Use of FetchSize

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

The FetchSize property can be set either on the OracleCommand or the OracleDataReader depending on the situation. Additionally, the FetchSize property of the OracleCommand is inherited by the OracleDataReader and can be modified.

If the FetchSize property is set on the OracleCommand, then the newly created OracleDataReader inherits the FetchSize property of the OracleCommand. This inherited FetchSize 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. All subsequent fetches from the database use the same cache allocated for that OracleDataReader. Therefore, changing the FetchSize after the first Read method invocation has no effect.

Fine-Tuning FetchSize

By fine-tuning the FetchSize property, applications can control memory usage and the number of rows fetched in one server round-trip for better performance. For example, if a query returns 100 rows and each row takes 1024 bytes, then setting FetchSize to 102400 takes just one server round-trip to fetch the hundred rows. For the same query, if the FetchSize is set to 10240, it takes 10 server 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 since it only fetches 10 rows and not 100 rows.

Using the RowSize Property

The RowSize property of the OracleCommand 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 by setting it to the product of RowSize and the number of rows to fetch for each server round-trip.

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

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