IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 [oracle@msdbdbadm01 ~]$
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.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
