Was this helpful?
[NO]UNION_FLATTEN
Turns union flattening ON or OFF for the session.
When set to ON, the opf_union_flatten resource causes OPF to search for union views or derived tables that can be combined with joins to potentially generate more efficient query plans.
When set to OFF, the resource prevents the application of the union flattening heuristic, possibly losing some optimization potential. This will use less OPF memory.
Union flattening is an optimization technique used to increase the potential for compiling efficient query plans. If a query involves joining the result of a union view (or derived table or common table element) to other tables or views, the query optimizer will expand the union view to produce a query that is the union of each SELECT in the union view joined to the other tables.
For example, if a view is defined as:
create view uv as select * from ul union all select * from u2"
and a query is executed as:
"select * from uv, a, b where uv. = a y and a.p = b.o"
the optimizer will transform it to
"select * from ul, a, b where ulx = ay and a.p = b.q union all select * from u2, a, b where u2.x = a.y and a.p = b.g"
This statement overrides the default set on the CBF opf_union_flatten configuration parameter for the current session.
The default is OFF.
 
Last modified date: 08/14/2024