SQLBLOCKMAX

The SQLBLOCKMAX option controls the maximum number of records retrieved from an Oracle relational database at one time. This option provides a means of fine-tuning the performance of data fetches.

Data type

INTEGER

Syntax

SQLBLOCKMAX = records

Arguments

records: An INTEGER that identifies the number of records you want fetched at one time. While you can set SQLBLOCKMAX to any INTEGER, no appreciable change in performance results in setting it over 100. The default is 10 records.

Notes

Opening Cursors

Only cursors opened after SQLBLOCKMAX is reset will use the new block size.

Number of Records

When a program typically opens a cursor, reads one record, and closes the cursor, you should set SQLBLOCKMAX to 1. Otherwise, the SQL FETCH statement retrieves 10 records and discards 9 of them. The same is true for other routine fetches of less than 10 records.

Block Size

When your program is fetching small records, you can increase SQLBLOCKMAX to reduce the number of blocks required for the fetch. Oracle OLAP fetches the data into a 64K buffer. The block size in bytes is the number of records multiplied by the size of the records. When the block size exceeds the 64K limit imposed by the buffer, Oracle OLAP automatically reduces the number of records fetched. See Example 22-20, "Defining a Cursor with SQLBLOCKMAX".

Examples

Example 22-20 Defining a Cursor with SQLBLOCKMAX

The following program fragment defines a cursor for fetching 50-byte records from a relational database. The new block size easily fits into Oracle OLAP's 64K buffer (50 bytes * 100 = 50k block size).

SQLBLOCKMAX = 100
SQL DECLARE CURSOR c1 FOR SELECT * FROM mydata
SQL OPEN c1