Sets an upper bound, expressed in megabytes, on the amount of temporary cache space that the optimizer can assume will be available for hash-partitioned hash-based query operators.
Integer from 0 to 4294967295
0
Can be set temporary for an individual connection, for a user, or for the PUBLIC group. No system privilege required to set this option. This option takes effect immediately.
When generating a query plan, the IQ optimizer might choose from several algorithms when processing a particular part of a query. These decisions often depend on estimates of the temp cache space that will be required to process that part of the query and on the currently available temp cache. This option sets an upper bound on estimated temporary space usage for operators that can consider a hash-partitioned hash-based algorithm.
The default value of 0 indicates that there is no hard upper bound, and that therefore optimizer’s choice will be limited only by the current temp cache availability, the current number of active user connections, and the HASH_PINNABLE_PERCENT option setting.
Note that this option affects only the optimizer’s algorithm selection decisions, and that the run-time usage may under some circumstances occasionally exceed this limit.