The MAXFETCH option sets an upper limit on the size of a data block generated by a FETCH statement specified in the OLAP_command parameter of the OLAP_TABLE function.

See also:

For more information on using FETCH statements, see FETCH. For more information on the OLAP_TABLE function, see the Oracle OLAP Reference.

Return Value



MAXFETCH = integer-expression



An INTEGER expression representing the maximum size in bytes of a data block generated by FETCH. The minimum value for MAXFETCH is 1K (approximately 1,000 bytes), and the maximum value is 2GB (approximately 2,000,000,000 bytes). The default value of MAXFETCH is 256K.


Improving Performance of Queries Using OLAP_TABLE

The setting of MAXFETCH can effect the performance of queries using the OLAP_TABLE function. Large queries with joins of OLAP_TABLE function may run faster with higher settings. However, larger settings use more memory which can cause slower performance when there are multiple users. The setting of MAXFETCH does not affect a SELECT using only one OLAP_TABLE function.

MAXFETCH can cause a FETCH error

When FETCH cannot package a data block within the size limit set by MAXFETCH, it produces an error, and no data is returned to the client. By setting MAXFETCH, you can produce an error, rather than run out of memory, when you attempt to fetch too much data.


Limiting Data Blocks to 4K

The following statement limits the size of data blocks to 4K.

  MAXFETCH = 4096