|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
SQLBLOCKMAX = 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
Only cursors opened after SQLBLOCKMAX is reset will use the new block size.
When a program typically opens a cursor, reads one record, and closes the cursor, you should set SQLBLOCKMAX to
1. Otherwise, the SQL FETCH command retrieves 10 records and discards 9 of them. The same is true for other routine fetches of less than 10 records.
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-19, "Defining a Cursor with SQLBLOCKMAX".
Example 22-19 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 (
50k block size).
SQLBLOCKMAX = 100 SQL DECLARE CURSOR c1 FOR SELECT * FROM mydata SQL OPEN c1