Merge Join Cartesian in Oracle

I will explain Merge Join Cartesian in Oracle in this post.

 

Merge Join Cartesian in Oracle

If you forget to use any WHERE Clause in the Join Condition, Probably Oracle will use the Merge Join Cartesian an this will affect the SQL Performance because The most expensive and bad join type is Merge Join cartesian.

Merge Join cartesian is prefered only for the small tables which have a very small number of rows.

 

 

 

 

If you forget to use Where Clause in Join Condition or Your tables are staled ( Stale statistics ) or missing join indexes, Oracle will use the Merge Join Cartesian.

Sometimes Oracle optimizer bugs are caused the Merge Join Cartesian.

 

For example; The following SQL Statement’s execution plan which used the Merge Join Cartesian is as follows.

SQL> select count(*) from dba_tables join all_tables using(table_name);

COUNT(*)
----------
70856

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ggqzux81vx4gw, child number 0
-------------------------------------
select count(*) from dba_tables join all_tables using(table_name)

Plan hash value: 451541934

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

| Id | Operation | Name | E-Rows | O
Mem | 1Mem | Used-Mem |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

| 0 | SELECT STATEMENT | | |
| | |

| 1 | SORT AGGREGATE | | 1 |
| | |

|* 2 | FILTER | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | |

| 3 | NESTED LOOPS OUTER | | 8236 |
| | |

|* 4 | HASH JOIN RIGHT OUTER | | 8236 | 5
219K| 5219K| 1612K (0)|

| 5 | INDEX FULL SCAN | I_USER2 | 157 | 1
025K| 1025K| |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 6 | HASH JOIN OUTER | | 8236 | 6
208K| 3974K| 7387K (0)|

|* 7 | HASH JOIN | | 8236 | 5
219K| 5219K| 1590K (0)|

| 8 | INDEX FULL SCAN | I_USER2 | 157 | 1
025K| 1025K| |

|* 9 | HASH JOIN | | 8236 | 5
596K| 5596K| 1446K (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 10 | TABLE ACCESS STORAGE FULL | TS$ | 23 | 1
025K| 1025K| |

|* 11 | HASH JOIN RIGHT OUTER | | 8236 | 3
990K| 3990K| 2587K (0)|

| 12 | TABLE ACCESS STORAGE FULL | SEG$ | 27589 | 1
025K| 1025K| |

|* 13 | HASH JOIN | | 8236 | 4

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
110K| 3653K| 3633K (0)|

|* 14 | TABLE ACCESS STORAGE FULL | TAB$ | 30303 | 1
025K| 1025K| |

|* 15 | HASH JOIN | | 46443 | 4
013K| 3380K| 3713K (0)|

| 16 | JOIN FILTER CREATE | :BF0000 | 30303 |
| | |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 17 | NESTED LOOPS OUTER | | 30303 |
| | |

|* 18 | HASH JOIN RIGHT OUTER | | 30303 | 4
904K| 4904K| 1612K (0)|

| 19 | INDEX FULL SCAN | I_USER2 | 157 | 1
025K| 1025K| |

|* 20 | HASH JOIN OUTER | | 30303 | 4
197K| 3053K| 3677K (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 21 | HASH JOIN | | 30303 | 5
219K| 5219K| 1609K (0)|

| 22 | INDEX FULL SCAN | I_USER2 | 157 | 1
025K| 1025K| |

|* 23 | HASH JOIN | | 30303 | 5
219K| 5219K| 1489K (0)|

| 24 | TABLE ACCESS STORAGE FULL | TS$ | 23 | 1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
025K| 1025K| |

|* 25 | HASH JOIN RIGHT OUTER | | 30303 | 3
885K| 3885K| 2609K (0)|

| 26 | TABLE ACCESS STORAGE FULL | SEG$ | 27589 | 1
025K| 1025K| |

|* 27 | HASH JOIN | | 30303 | 4
110K| 3653K| 3633K (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 28 | TABLE ACCESS STORAGE FULL | TAB$ | 30303 | 1
025K| 1025K| |

| 29 | MERGE JOIN CARTESIAN | | 170K|
| | |

| 30 | MERGE JOIN CARTESIAN | | 1 |
| | |

|* 31 | HASH JOIN | | 1 | 4
904K| 4904K| 416K (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 32 | FIXED TABLE FULL | X$KSPPI | 1 |
| | |

| 33 | FIXED TABLE FULL | X$KSPPCV | 3978 |
| | |

| 34 | BUFFER SORT | | 1 | 73
728 | 73728 | |

| 35 | VIEW | | 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | |

|* 36 | HASH JOIN | | 1 | 4
904K| 4904K| 436K (0)|

|* 37 | FIXED TABLE FULL | X$KSPPI | 1 |
| | |

| 38 | FIXED TABLE FULL | X$KSPPCV | 3978 |
| | |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 39 | BUFFER SORT | | 170K|
13M| 2178K| 11M (0)|

|* 40 | TABLE ACCESS STORAGE FULL| OBJ$ | 170K| 1
025K| 1025K| |

| 41 | INDEX STORAGE FAST FULL SCAN | I_OBJ1 | 199K| 1
025K| 1025K| |

|* 42 | INDEX RANGE SCAN | I_OBJ1 | 1 | 1
025K| 1025K| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 43 | JOIN FILTER USE | :BF0000 | 170K|
| | |

|* 44 | TABLE ACCESS STORAGE FULL | OBJ$ | 170K| 1
025K| 1025K| |

| 45 | INDEX STORAGE FAST FULL SCAN | I_OBJ1 | 199K| 1
025K| 1025K| |

|* 46 | INDEX RANGE SCAN | I_OBJ1 | 1 | 1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
025K| 1025K| |

| 47 | NESTED LOOPS SEMI | | 1 |
| | |

| 48 | FIXED TABLE FULL | X$KZSRO | 2 |
| | |

|* 49 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 1
025K| 1025K| |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 50 | FIXED TABLE FULL | X$KZSPR | 2 |
| | |

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


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

2 - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
TO_NUMBER(SYS_CONTEXT('USERENV','CURRENT_USERID'))=2147483617 OR
IS NOT NULL OR IS NOT NULL))

4 - access("CX"."OWNER#"="CU"."USER#")
6 - access("T"."DATAOBJ#"="CX"."OBJ#")
7 - access("O"."OWNER#"="U"."USER#")
9 - access("T"."TS#"="TS"."TS#")
11 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#
"="S"."TS#")

13 - access("O"."OBJ#"="T"."OBJ#")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
14 - filter(BITAND("T"."PROPERTY",1)=0)
15 - access("O"."NAME"="O"."NAME")
18 - access("CX"."OWNER#"="CU"."USER#")
20 - access("T"."DATAOBJ#"="CX"."OBJ#")
21 - access("O"."OWNER#"="U"."USER#")
23 - access("T"."TS#"="TS"."TS#")
25 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#
"="S"."TS#")

27 - access("O"."OBJ#"="T"."OBJ#")
28 - filter(BITAND("T"."PROPERTY",1)=0)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
31 - access("KSPPI"."INDX"="KSPPCV"."INDX")
32 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
36 - access("KSPPI"."INDX"="KSPPCV"."INDX")
37 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
40 - storage(BITAND("O"."FLAGS",128)=0)
filter(BITAND("O"."FLAGS",128)=0)
42 - access("T"."BOBJ#"="CO"."OBJ#")
44 - storage((BITAND("O"."FLAGS",128)=0 AND SYS_OP_BLOOM_FILTER(:BF0000,"O"."N
AME")))

filter((BITAND("O"."FLAGS",128)=0 AND SYS_OP_BLOOM_FILTER(:BF0000,"O"."NA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ME")))

46 - access("T"."BOBJ#"="CO"."OBJ#")
49 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
50 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-39
7) OR

(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))
AND

"INST_ID"=USERENV('INSTANCE')))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

- 4 Sql Plan Directives used for this statement


106 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[[email protected] ~]$

 

 

 

 

 

Oracle Sort-merge join

Sort-merge join is used to join rows from two independent sources (Tables). All row sources are sorted by the same sort key, then sorted rows are merged as follows.

 

 

 

If one or more row sources are already sorted, then sort merge join is prefered instead of hash join, if not Hash join is generally better than sort merge join.

 

If the join condition of tables is an inequality condition  ( such as <, <=, >, or >= ) and sorting is required, then Oracle optimizer can select the Sort merge join instead of Hash join for joining large data sets ( Large Tables, Views ).

 

You can use USE_MERGE hint in the SQL Statement to instruct the optimizer for using Sort merge join.

 

 

You can read the following post to learn more details about Performance Tuning in Oracle.

Optimizer Access Paths & Join Operators ( Hash Join, Nested Loop ) in Oracle | Oracle Database Performance Tuning Tutorial -8

 

SQL Tuning Tips and Tricks Tutorial in Oracle -3

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 4,280 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply