Oracle Database 10g -- Disable Merge Join Cartesian Operation

Problem

Sometimes due to environmental factors, the optimizer may select sub-optimal execution plans with the MERGE JOIN CARTESIAN operation

You want to disable the MERGE JOIN CARTESIAN operation

Solution

To disable the MERGE JOIN CARTESIAN operation at session level:

SQL> ALTER SESSION SET "_optimizer_cartesian_enabled" = FALSE;

To disable the MERGE JOIN CARTESIAN operation at system level:

SQL> ALTER SYSTEM SET "_optimizer_cartesian_enabled" = FALSE SID='*' SCOPE=BOTH;