Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian join (merged 3)
icon5.gif  Cartesian join (merged 3) [message #413418] Wed, 15 July 2009 11:22 Go to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
We are facing some peculiar issue where the explain plan generated by the same sql causes Cartesian join on one and not on other two. Apart from the size of the data all three environments are identical with respect to DB version, parameters and such. Any help or guidance will be much appreciated.

The SQL..
SELECT 
  T5.INTEGRATION_ID AS SOLUTIONID,
  T3.INTEGRATION_ID AS INTEGRATION_ID,
  T3.CALLINGINSTANCEID AS CALLINGINSTANCEID
FROM 
  WC_FMO_PROJECT2OBJXREF_D T1,
  WC_FMO_V2_CONNECTION_D T2 ,
  WC_FMO_IPLOGICALCHANNEL_D T3,
  WC_FMO_PROJECT_D T4,
  WC_FMO_SOLUTION_D T5
WHERE 
  T1.CALLINGINSTANCEID = T2.INTEGRATION_ID AND
  T1.PROJECTID = T4.INTEGRATION_ID   and
  T2.INTEGRATION_ID = T3.CALLINGINSTANCEID   AND
  T4.SOLUTION_ID = T5.INTEGRATION_ID;


Explain plan on Env1 (where we have the issue with Cartesian join)
SELECT STATEMENT  CHOOSECost: 18  Bytes: 66  Cardinality: 1  						
	11 TABLE ACCESS BY INDEX ROWID SIEBEL.WC_FMO_PROJECT2OBJXREF_D Cost: 2  Bytes: 16  Cardinality: 1  					
		10 NESTED LOOPS  Cost: 18  Bytes: 66  Cardinality: 1  				
			8 NESTED LOOPS  Cost: 16  Bytes: 50  Cardinality: 1  			
				6 MERGE JOIN CARTESIAN  Cost: 15  Bytes: 42  Cardinality: 1  		
					3 HASH JOIN  Cost: 11  Bytes: 24  Cardinality: 1  	
						1 TABLE ACCESS FULL SIEBEL.WC_FMO_IPLOGICALCHANNEL_D Cost: 3  Bytes: 76,320  Cardinality: 4,770  
						2 TABLE ACCESS FULL SIEBEL.WC_FMO_V2_CONNECTION_D Cost: 6  Bytes: 134,296  Cardinality: 16,787  
					5 BUFFER SORT  Cost: 9  Bytes: 160,794  Cardinality: 8,933  	
						4 TABLE ACCESS FULL SIEBEL.WC_FMO_PROJECT_D Cost: 4  Bytes: 160,794  Cardinality: 8,933  
				7 INDEX RANGE SCAN NON-UNIQUE SIEBEL.WC_FMO_SOLUTION_D_IX2 Cost: 1  Bytes: 8  Cardinality: 1  		
			9 INDEX RANGE SCAN NON-UNIQUE SIEBEL.WC_FMO_PROJECT2OBJXREF_D_IX3 Cost: 1  Cardinality: 1  		


Explain plan on Env2 (No issues here)
SELECT STATEMENT  CHOOSECost: 86  Bytes: 256,674  Cardinality: 3,889  					
	9 HASH JOIN  Cost: 86  Bytes: 256,674  Cardinality: 3,889  				
		7 HASH JOIN  Cost: 75  Bytes: 232,406  Cardinality: 4,007  			
			1 TABLE ACCESS FULL SIEBEL.WC_FMO_PROJECT_D Cost: 4  Bytes: 153,720  Cardinality: 8,540  		
			6 HASH JOIN  Cost: 68  Bytes: 254,560  Cardinality: 6,364  		
				4 HASH JOIN  Cost: 11  Bytes: 126,000  Cardinality: 5,250  	
					2 TABLE ACCESS FULL SIEBEL.WC_FMO_IPLOGICALCHANNEL_D Cost: 3  Bytes: 84,032  Cardinality: 5,252  
					3 TABLE ACCESS FULL SIEBEL.WC_FMO_V2_CONNECTION_D Cost: 6  Bytes: 138,824  Cardinality: 17,353  
				5 TABLE ACCESS FULL SIEBEL.WC_FMO_PROJECT2OBJXREF_D Cost: 49  Bytes: 2,967,312  Cardinality: 185,457  	
		8 INDEX FAST FULL SCAN NON-UNIQUE SIEBEL.WC_FMO_SOLUTION_D_IX2 Cost: 6  Bytes: 520,240  Cardinality: 65,030 
Re: Cartesian join (merged 3) [message #413428 is a reply to message #413418] Wed, 15 July 2009 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT 
  T5.INTEGRATION_ID AS SOLUTIONID,
  T3.INTEGRATION_ID AS INTEGRATION_ID,
  T3.CALLINGINSTANCEID AS CALLINGINSTANCEID
FROM 
  WC_FMO_PROJECT2OBJXREF_D T1,
  WC_FMO_V2_CONNECTION_D T2 ,
  WC_FMO_IPLOGICALCHANNEL_D T3,
  WC_FMO_PROJECT_D T4,
  WC_FMO_SOLUTION_D T5
WHERE 
  T1.CALLINGINSTANCEID = T2.INTEGRATION_ID AND
  T1.PROJECTID = T4.INTEGRATION_ID   and
  T2.INTEGRATION_ID = T3.CALLINGINSTANCEID   AND
  T4.SOLUTION_ID = T5.INTEGRATION_ID;

Only 2 tables contribute data to the SELECT clause.
Therefore only T3 & T5 tables should be in the FROM column.
Additional WHERE clause filter is needed to eliminate the Cartesian Product.
Or eliminate reference to T1 or T2 or T4 completely.
Since I don't know tables or data, I can't make more specific recommendation.

[Updated on: Wed, 15 July 2009 12:02]

Report message to a moderator

Re: Cartesian join (merged 3) [message #413432 is a reply to message #413428] Wed, 15 July 2009 12:05 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 15 July 2009 17:48
Only 2 columns contribute data to the SELECT clause.
Therefore only T3 & T5 tables should be in the FROM column.
Or eliminate reference to T1 or T2 or T4 completely.


Looks to me like T1,T2 and T4 are linking tables for the other two so I'm really not sure how you think they can be got rid of or even removed from the FROM clause.

BlackSwan wrote on Wed, 15 July 2009 17:48

Additional WHERE clause filter is needed to eliminate the Cartesian Product.


I really doubt that is necessary.

@mechos

Cartesian joins in an explain plan aren't necessarily a problem you know. Oracle can use them if it thinks one of the data sets it's joining together only has 1 row - which it does in this case:
6 MERGE JOIN CARTESIAN  Cost: 15  Bytes: 42  Cardinality: 1 

the important bit there is the cardinality.

Ask your self these questions:
1) Is it giving the right results?
2) Is it giving the results quickly?

If the answer to both of these is yes then the presence of the cartesian join step in the plan is not something you need to worry about.

As for why it's different on other machines - you've got different amounts of data - this can lead to different plans.
Re: Cartesian join (merged 3) [message #413434 is a reply to message #413418] Wed, 15 July 2009 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I'm really not sure how you think they can be got rid of or even removed from the FROM clause.
They CAN be removed out of the FROM clause & subordinated into the WHERE clause.
Re: Cartesian join (merged 3) [message #413436 is a reply to message #413418] Wed, 15 July 2009 12:18 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Will the absence of indexes for a particular table from one env to the other create Cartesian join?
Re: Cartesian join (merged 3) [message #413438 is a reply to message #413436] Wed, 15 July 2009 12:44 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
mechos wrote on Wed, 15 July 2009 13:18
Will the absence of indexes for a particular table from one env to the other create Cartesian join?


Sure.

You say you have a "problem" with the Cartesian Join, but look at the Cost (less) and bytes (much less) for the explain plan with the Cartesian join in additional to what cookiemonster said about the cardinality.

Also, do you have updated statistics in both environments?
Re: Cartesian join (merged 3) [message #413442 is a reply to message #413418] Wed, 15 July 2009 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT t5.integration_id    AS solutionid,
       t3.integration_id    AS integration_id,
       t3.callinginstanceid AS callinginstanceid
FROM   wc_fmo_iplogicalchannel_d t3,
       wc_fmo_solution_d t5
WHERE  t5.integration_id IN (SELECT t4.solution_id
                             FROM   wc_fmo_project_d t4
                             WHERE  t4.integration_id IN (SELECT t1.projectid
                                                          FROM   wc_fmo_project2objxref_d t1
                                                          WHERE  t1.callinginstanceid IN (SELECT t2.integration_id
                                                                                          FROM   wc_fmo_v2_connection_d t2
                                                                                          WHERE  t2.integration_id = t3.callinginstanceid))); 
Re: Cartesian join (merged 3) [message #413514 is a reply to message #413418] Thu, 16 July 2009 03:45 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Agree with Cookiemonster Sir...

Quote:

There are several situations where Oracle will use a Cartesian Product

* You have not specified any join conditions in the WHERE clause of your SQL. For example:

SELECT a.column1, b.column3
FROM table_a a, table_b b
WHERE a.column9 = 'X'
AND b.column5 >= to_date('13-JAN-2003','DD-MON-YYYY')

This is usually a programming error. Add join conditions and re-test. Better still, use ANSI join syntax in V9i+; it will force you to supply a join predicate (unless you specify the CROSS JOIN method).
*

Oracle thinks that one of the tables in the join has zero or one rows. If this is actually the case, the Cartiesian Product is fine. Otherwise it is likely that the statistics are missing on one or both tables: check the statistics and recalculate if necessary.
*

Oracle is performing a Star Query. However this usually only happens when you supply the STAR hint. Try it without the STAR hint and see if it imporves.
*

Oracle is performing a Star Join. However this usually only happens when you supply the ORDERED hint. Try it without the ORDERED hint and see if it imporves.

If you have appropriate join predicates, up to date statistics, no hints, and tables selecting 0 or 1 row, then Oracle should not be performing a Cartesian Product. If it is still happending, look at your join prediactes and consider the best order to join the tables. Place the tables in the FROM clause in that order and add the ORDERED hint to the SQL. This should stop the cartesian product. If it is still slow, continue reading this Guide for other alternatives.

Source: http://www.orafaq.com/tuningguide/



Also make sure that statistics are gathered recently on both the env.

It also depends on factors like are you getting same data in two env? Are the indexes the same on the two env for those tables?


Thanks
Previous Topic: pl/sql
Next Topic: SQL query using NVL
Goto Forum:
  


Current Time: Thu Feb 13 12:13:31 CST 2025