Sybase 12.4.2 manual Temporary Store, 373

SET OPTION

CHAPTER 10 Managing User IDs and Permissions

Setting options

Resources that can be managed

For example, you may wish to prevent a single connection from taking too much of the available memory or CPU resources, so that one connection does not slow down other users of the database.

Adaptive Server IQ provides a set of database options that the DBA can use to control resources. These options are called resource governors.

You can set database options using the SET OPTION statement, which has the following syntax:

SET [ TEMPORARY ] OPTION

... [ userid. PUBLIC. ]option-name = [ option-value ]

For reference information about options, see “Database Options” in Adaptive

Server IQ Reference Manual. For information on thestatement, see Adaptive Server IQ Reference Manual.

The following options can be used to manage resources. See Chapter 12, “Managing System Resources” or see the Adaptive Server IQ Reference Manual for more information on these options.

•AGGREGATION_CUTOFF Sets the precision level at which Adaptive Server IQ uses a more efficient internal storage type to do calculations on SUM or AVG numeric expressions.

•CURSOR_WINDOW_ROWS Defines the number of cursor rows to buffer.

•LOAD_MEMORY_MB Sets an upper bound for the amount of heap memory that subsequent load operations can use.

•MAIN_CACHE_MEMORY_MB Sets the size of the cache for the main IQ Store.

•MAX_CARTESIAN_RESULT Limits the number of result rows from a query containing a cartesian join.

•	MAX_IQ_THREADS_PER_CONNECTION		Sets the number of
	processing threads available to a connection for use in IQ operations.
•	TEMP_CACHE_MEMORY_MB	Sets the size of the cache for the IQ
	Temporary Store.

•JOIN_OPTIMIZATION Enables optimization of join order. When this option is on (default), Adaptive Server IQ optimizes the join order to reduce the size of intermediate results and sorts, and to balance the system load.

The following options affect the database engine, but have limited impact on Adaptive Server IQ:

373