Merge join Cartesian in Oracle

In the Execution Plan, the CARTESIAN cartesian Product sometimes appears. Simply put, What Is CARTESIAN? There are two sets. Any member of each set must be associated with any member of another set... below are some experiments on cartesian:

SQL> set linesize 2000

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

T TABLE

REP_T_LOG TABLE

SQL> select * from t, rep_t_log

Execution Plan

----------------------------------------------------------

Plan hash value: 2235198130

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

----------------------------------------------------------------------------------

| 0 | select statement | 20000 | 429K | 16 (7) | 00:00:01 |

| 1 | merge join cartesian | 20000 | 429K | 16 (7) | 00:00:01 |

| 2 | table access full | REP_T_LOG | 2 | 30 | 3 (0) | 00:00:01 |

| 3 | buffer sort | 10000 | 70000 | 13 (8) | 00:00:01 |

| 4 | table access full | T | 10000 | 70000 | 6 (0) | 00:00:01 |

----------------------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement

SQL>

If merge join Cartesian is not allowed, you can set the implicit parameter "_ optimizer_mjc_enabled"

SQL> alter session set "_ optimizer_mjc_enabled" = false;

Session altered.

SQL> select * from t, rep_t_log;

Execution Plan

----------------------------------------------------------

Plan hash value: 4018263157

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------------

| 0 | select statement | 20000 | 429K | 16 (7) | 00:00:01 |

| 1 | nested loops | 20000 | 429K | 16 (7) | 00:00:01 |

| 2 | table access full | REP_T_LOG | 2 | 30 | 3 (0) | 00:00:01 |

| 3 | table access full | T | 10000 | 70000 | 6 (0) | 00:00:01 |

--------------------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement

SQL> conn/as sysdba

Connected.

Disable Cartesian product throughout the system level

SQL> alter system set "_ optimizer_mjc_enabled" = false;

System altered.

SQL> conn test/test

Connected.

SQL> set autotrace trace exp

SQL> select * from t, rep_t_log;

Execution Plan

----------------------------------------------------------

Plan hash value: 4018263157

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------------

| 0 | select statement | 20000 | 429K | 16 (7) | 00:00:01 |

| 1 | nested loops | 20000 | 429K | 16 (7) | 00:00:01 |

| 2 | table access full | REP_T_LOG | 2 | 30 | 3 (0) | 00:00:01 |

| 3 | table access full | T | 10000 | 70000 | 6 (0) | 00:00:01 |

--------------------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement

When merge join Cartesian occurs, the SQL Execution efficiency may be very low. The hidden parameters of the system should not be changed.