The query optimizer includes a heuristic that searches for common tables in different fragments of a query. For example, in the query SELECT ... FROM a, b, c WHERE a.x = b.y AND ... UNION SELECT ... FROM a, b, d WHERE a.x = b.y and ..., the "a, b" join is shared between the union’ed selects. In such cases query plans are built that materialize the common portions once and cache them in memory to be reused. This "reuse" heuristic can greatly improve the performance of some queries.

In rare cases, the cached fragments may overflow the available memory. The heuristic attempts to avoid overflow by using estimates of the size of the cached results, but you can also turn off the feature by using the SET NOREUSE statement. SET NOREUSE stays in effect until the end of the session or until a SET REUSE statement is issued to reinstate the default behavior.

If you want the NOREUSE behavior to be the default for the server, you can set the parameter opf_reuse to OFF in config.dat. You must then use the SET REUSE statement to enable the heuristic for a given session.

The parameter opf_maxreuse in config.dat specifies the maximum estimated size of a reuse fragment (as a nonnegative number of bytes) that can be cached. If the estimate for a potential reuse fragment of a query exceeds that amount, the results of the reuse fragment are not cached and are materialized as many times as referenced in a given query. The default value for opf_maxreuse is 100 MB. If it is explicitly set to 0, fragment size is not checked and the reuse feature is always attempted.