Enabling and disabling merge joins

By default, merge joins are not enabled at the server level. When merge joins are disabled, the server only costs nested-loop joins, and merge joins are not considered. To enable merge joins server-wide, set enable sort-merge joins and JTC to 1. This also enables join transitive closure.

The command set sort_merge on overrides the server level to allow use of merge joins in a session or stored procedure.

To enable merge joins, use:

set sort_merge on

To disable merge joins, use:

set sort_merge off

For information on configuring merge joins server-wide see the System Administration Guide.