Estimating Temporary Space

You can use the following guidelines to estimate the amount of temporary space to allocate:

• For OLTP applications, allocate temporary dbspaces that equal at least 10 percent of the table.
• For DSS applications, allocate temporary dbspaces that equal at least 50 percent of the table.

Hash joins can use a significant amount of memory and can potentially overflow to temporary space on disk. You can use the following formula to estimate the amount of memory that is required for the hash table in a hash join:

hash_table_size = (32 bytes + row_size) * num_rows_smalltab

The value for num_rows_smalltab should be the number of rows in the probe table. For more information on hash joins, see Hash Join.

You can use the DS_NONPDQ_QUERY_MEM configuration parameter to configure sort memory for all queries except PDQ queries. For more information, see More Memory for Queries with Hash Joins, Aggregates, and Other Memory-Intensive Elements