HINT避免MERGE JOIN/*+ OPT_PARAM(\'_optimizer_mjc_enabled\',\'false/true\')

本文链接：https://blog.youkuaiyun.com/duqiangatom/article/details/8309134

本文介绍了如何避免在Oracle中使用MERGE JOIN，包括在系统级别设置隐藏参数_optimizer_mjc_enabled为false，通过登陆触发器设定SESSION级别的该参数，以及在单个SQL语句中使用HINT /*+ OPT_PARAM('_optimizer_mjc_enabled','false') */进行禁止。通过示例展示了这些方法的效果，以提高SQL执行效率。" 51781210,2922969,使用Physijs实现three.js物理引擎,"['JavaScript', 'three.js', 'webgl', '物理引擎']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--alter session set “_optimizer_ignore_hints”=TRUE;

-- select /*+ opt_param(‘parallel_execution_enabled’, ‘false’) */ ..

--swap_join_input(tab) hint to swap the order of the tables going through the join

1、避免的方法基本就是把MERGE JOIN给打死，可以在系统级别设置隐含参数_optimizer_mjc_enabled=false；

2、也可以在登陆触发器中设置SESSION级别的_optimizer_mjc_enabled=false；

alter session set "_optimizer_mjc_enabled"=false; --10R1

alter session set "_optimizer_cartesian_enabled "=false ; --10R2 plus

3、也可以在单个SQL级别使用HINT来禁止，/*+ OPT_PARAM('_optimizer_mjc_enabled','false') */，顺便提一下强悍的OPT_PARAM，这玩意可以让你在SQL级别通过加HINT的方式来调整单个SQL执行的时候所依赖的系统参数的值，相当强悍。这个是 10.2版本新引进的参数，至于哪些参数可以在这个HINT中设置，我也不知道，不过估计SESSION级别的参数应该都可以的。

eg ：

create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
　　create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
　　create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
　　create index inx_col12A on a(col1,col2);

1）========================================================

SQL> set timing on
SQL> set autot traceonly
SQL> SELECT   A.COL4
2    FROM B, A, C
3   WHERE B.COL3 = 10
4     AND A.COL1 = B.COL1
5     AND A.COL2 = C.COL2
6     AND C.COL3 = 5;

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 4163918451

------------------------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   110 |     7 (15)| 00:00:01 |
|* 1 | HASH JOIN            |      |     1 |   110 |     7 (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     4   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL | B    |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     2   (0)| 00:00:01 |
|* 5 |     TABLE ACCESS FULL | C    |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | A    |     1 |    58 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")
   3 - filter("B"."COL3"=10)
   5 - filter("C"."COL3"=5)

2）=============================================================

SQL> SELECT /*+ rule */ A.COL4
2    FROM B, A, C
3   WHERE B.COL3 = 10
4     AND A.COL1 = B.COL1
5     AND A.COL2 = C.COL2
6     AND C.COL3 = 5;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
-------------------------------------------------------
Plan hash value: 800391165

----------------------------------------------------
| Id | Operation                     | Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT              |            |
|   1 | MERGE JOIN                   |            |
|   2 |   SORT JOIN                   |            |
|   3 |    TABLE ACCESS BY INDEX ROWID| A          |
|   4 |     NESTED LOOPS              |            |
|* 5 |      TABLE ACCESS FULL        | B          |
|* 6 |      INDEX RANGE SCAN         | INX_COL12A |
|* 7 |   SORT JOIN                   |            |
|* 8 |    TABLE ACCESS FULL          | C          |
----------------------------------------------------

3）================================================================

SQL> SELECT /*+ opt_param('_optimizer_mjc_enabled','false') */ A.COL4
2    FROM B, A, C
3   WHERE B.COL3 = 10
4     AND A.COL1 = B.COL1
5     AND A.COL2 = C.COL2
6     AND C.COL3 = 5;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2102118802

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   110 |     7 (15)| 00:00:01 |
|* 1 | HASH JOIN          |      |     1 |   110 |     7 (15)| 00:00:01 |
|   2 |   NESTED LOOPS      |      |     1 |    52 |     4   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| B    |     1 |    26 |     2   (0)| 00:00:01 |
|* 4 |    TABLE ACCESS FULL| C    |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | A    |     1 |    58 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")
   3 - filter("B"."COL3"=10)
   4 - filter("C"."COL3"=5)