|Oracle® OLAP DML Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
SQLBLOCKMAX = records
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 statement 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 6-101, "Defining a Cursor with SQLBLOCKMAX".
The following program fragment defines a cursor for fetching 50-byte records from a 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