FORCE_DISK (UNION, INTERSECT, and EXCEPT Option)
FORCE_DISK causes the query engine to use disk instead of memory for temporary storage of the data required to process UNION, INTERSECT, or EXCEPT operators. This frees memory for other server operations. FORCE_DISK is useful for queries that consume a large amount of memory and affect performance of other processes running on the server.
Note: UNION ALL will not force data to disk unless PARALLEL is also specified in the OPTION.
If FORCE_DISK is not specified, the query engine uses memory instead of disk whenever possible.
When the FORCE_DISK option is specified on the SELECT level of a query, it is applied over all nodes and takes precedence even if FORCE_DISK is set to FALSE elsewhere in the query.
Operators
UNION, INTERSECT, EXCEPT
Syntax
force_disk
Example
SELECT column1 FROM table1
UNION {OPTION force_disk}
SELECT column1 FROM table2