MAXFETCH

The MAXFETCH option sets an upper limit on the size of a data block generated by a FETCH command specified in the OLAP_command parameter of the OLAP_TABLE function. For more information on the FETCH command, see FETCH. For more information on the OLAP_TABLE function, see the Oracle OLAP Reference.

Return Value

INTEGER

Syntax

MAXFETCH = integer-expression

Arguments

integer-expression

An 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.

Notes

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.

Examples

Limiting Data Blocks to 4K

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

MAXFETCH = 4096