Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian join (merged 3)
Cartesian join (merged 3) [message #413418] |
Wed, 15 July 2009 11:22  |
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   |
 |
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   |
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 #413438 is a reply to message #413436] |
Wed, 15 July 2009 12:44   |
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 #413514 is a reply to message #413418] |
Thu, 16 July 2009 03:45  |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 12:13:31 CST 2025
|