Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

B28126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

SQLBLOCKMAX

The SQLBLOCKMAX option controls the maximum number of records retrieved from an Oracle 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 6-101, "Defining a Cursor with SQLBLOCKMAX".

Examples

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 (50 bytes * 100 = 50k block size).

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