Oracle Array Blocking for SELECT Requests

The Oracle Data Adapter supports array retrieval from result sets produced by executing SELECT queries or stored procedures. This technique substantially reduces network traffic and CPU utilization.

High FETCHSIZE values increase the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. The default value is 20; a value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

Syntax: How to Set FETCHSIZE for Oracle

The block size for a SELECT request applies to TABLE FILE requests, MODIFY requests, MATCH requests, and DIRECT SQL SELECT statements.

SQL [SQLORA] SET FETCHSIZE n

where:

SQLORA

Indicates the Oracle Data Adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be retrieved at once using array retrieval techniques. Accepted values are 1 to 5000. The default is 20.