Home » SQL & PL/SQL » SQL & PL/SQL » Merge join cartesian (Oracle 10.2.0.1.0)
Merge join cartesian [message #398734] Fri, 17 April 2009 05:19 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi,

I have a query as shown below:

select TO_CHAR(odi.as_of_date, 'DD/MM/YYYY') AS_OF_DATE,
                               fmodb.folder_name Folder,
                               fmodt.short_desc Allocation_name,
                               fatd.alloc_type as alloc_type
                          from fsi_data_identity          odi,
                               fsi_m_object_definition_b  fmodb,
                               fsi_m_object_definition_tl fmodt,
                               FSI_M_ALLOCATION_RULE      fia,
                               fsi_alloc_type_dsc         fatd
                         where odi.description = fmodb.object_definition_id
                           and fmodb.object_definition_id =
                               fmodt.object_definition_id
                           and fmodb.object_definition_id =
                               fia.allocation_sys_id
                           and fatd.ALLOC_TYPE_CD = fia.allocation_type_cd
                           and fmodb.id_type = 0


In the explain plan output i see merge cartesian join being used. Can you explain why Oracle choses cartesian join when i have given the joins properly? I am afraid my understaing on cartesian join is wrong.

The explain plan o/p is as under:
1	Plan hash value: 2112157228
2	 
3	------------------------------------------------------------------------------------------------------
4	| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
5	------------------------------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT        |                            |     1 |   134 |    16   (7)| 00:00:01 |
7	|*  1 |  HASH JOIN              |                            |     1 |   134 |    13   (8)| 00:00:01 |
8	|   2 |   MERGE JOIN CARTESIAN  |                            |     1 |    91 |    11  (10)| 00:00:01 |
9	|*  3 |    HASH JOIN            |                            |     1 |    76 |     8  (13)| 00:00:01 |
10	|   4 |     MERGE JOIN CARTESIAN|                            |     1 |    68 |     4   (0)| 00:00:01 |
11	|   5 |      TABLE ACCESS FULL  | FSI_M_OBJECT_DEFINITION_TL |     1 |    30 |     2   (0)| 00:00:01 |
12	|   6 |      BUFFER SORT        |                            |     1 |    38 |     2   (0)| 00:00:01 |
13	|*  7 |       TABLE ACCESS FULL | FSI_ALLOC_TYPE_MLS         |     1 |    38 |     2   (0)| 00:00:01 |
14	|*  8 |        TABLE ACCESS FULL| FSI_MLS                    |     1 |     7 |     3   (0)| 00:00:01 |
15	|   9 |     TABLE ACCESS FULL   | FSI_M_ALLOCATION_RULE      |     1 |     8 |     3   (0)| 00:00:01 |
16	|  10 |    BUFFER SORT          |                            |     3 |    45 |     8  (13)| 00:00:01 |
17	|  11 |     TABLE ACCESS FULL   | FSI_DATA_IDENTITY          |     3 |    45 |     3   (0)| 00:00:01 |
18	|* 12 |   TABLE ACCESS FULL     | FSI_M_OBJECT_DEFINITION_B  |     1 |    43 |     2   (0)| 00:00:01 |
19	------------------------------------------------------------------------------------------------------
20	 
21	Predicate Information (identified by operation id):
22	---------------------------------------------------
23	 
24	   1 - access("FMODB"."OBJECT_DEFINITION_ID"=TO_NUMBER("ODI"."DESCRIPTION") AND 
25	              "FMODB"."OBJECT_DEFINITION_ID"="FMODT"."OBJECT_DEFINITION_ID" AND 
26	              "FMODB"."OBJECT_DEFINITION_ID"="FIA"."ALLOCATION_SYS_ID")
27	   3 - access("ALLOC_TYPE_CD"="FIA"."ALLOCATION_TYPE_CD")
28	   7 - filter("MLS_CD"= (SELECT /*+ */ DECODE("INSTALLED_FLG",1,"MLS_CD",'US') FROM 
29	              PROFITATOMIC."FSI_MLS" "FSI_MLS" WHERE "MLS_CD"=USERENV('LANG')))
30	   8 - filter("MLS_CD"=USERENV('LANG'))
31	  12 - filter("FMODB"."ID_TYPE"=0)

Any help would be appreciated.

Thank you!
Senthil
Re: Merge join cartesian [message #398787 is a reply to message #398734] Fri, 17 April 2009 09:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I guess it is because of the very small number of rows returned.
May be the there are two tables with very less records that are joined with a larger table.
If so, it is exactly a documented behavior.
Re: Merge join cartesian [message #399007 is a reply to message #398787] Mon, 20 April 2009 04:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The optimizer thinks that there are very few rows in your tables, and that it's going to be quick and easy to simply to a cartesian join between them.
If this isn't the case, then you need to refresh the statistics on your tables.
Previous Topic: Heirarchical Query, Connect By Loop in User Data
Next Topic: Faster deletion
Goto Forum:
  


Current Time: Mon Dec 05 14:38:07 CST 2016

Total time taken to generate the page: 0.16293 seconds