Home » RDBMS Server » Performance Tuning » how avoid merge join cartesian. (Oracle 11g)
how avoid merge join cartesian. [message #602611] Thu, 05 December 2013 23:10 Go to next message
na.dharma@gmail.com
Messages: 74
Registered: May 2008
Location: bangalore
Member

Hi

I have a query which is running with merge join
cartesian. can anybody help me to how can i avoid merge join cartesian
SELECT /*+ index(a_adjustment_audit.a_adj_audit_pk) */
              audit_metadata_1 ticket_no,
               audit_metadata_2 original_trade_reference,
               audit_metadata_3 trade_id,
               row_number() over (order by row_created_dttm asc) version_no,
               audit_user_id user_id,
               team team,
               row_created_dttm row_created_dttm,
               TO_NUMBER(audit_metadata_4) original_value,
               TO_NUMBER(audit_metadata_5) current_value,
               TO_NUMBER(audit_metadata_5) - TO_NUMBER(audit_metadata_4) adjusted_value,
               adjustment_comment adjustment_comment,
               fs.sensitivity_type_code sensitivity_type
          FROM a_adjustment_audit aud INNER JOIN f_sensitivity fs ON (aud.row_bridge_key = fs.sensitivity_key)
         WHERE aud.table_name = 'F_SENSITIVITY' AND
               aud.row_bridge_key = 1816094285 AND
               ROWNUM < 1001

SELECT STATEMENT, GOAL = ALL_ROWS			1510	1	135
 WINDOW NOSORT			1510	1	135
  COUNT STOPKEY					
   MERGE JOIN CARTESIAN			1509	1	135
    TABLE ACCESS BY INDEX ROWID	CRIS_WAREHOUSE_USER	A_ADJUSTMENT_AUDIT	2	1	118
     INDEX RANGE SCAN	CRIS_WAREHOUSE_USER	A_ADJ_AUDIT_PK	1	1	
    BUFFER SORT			1508	1	17
     PARTITION LIST ALL			1507	1	17
      PARTITION LIST ALL			1507	1	17
       INDEX SKIP SCAN	CRIS_WAREHOUSE_USER	F_SENSITIVITY_PK	1507	1	17


*BlackSwan added {code} tags. Please do so yourself in the future.
See URL below
http://www.orafaq.com/forum/t/174502/

[Updated on: Thu, 05 December 2013 23:19] by Moderator

Report message to a moderator

Re: how avoid merge join cartesian. [message #602625 is a reply to message #602611] Fri, 06 December 2013 01:35 Go to previous messageGo to next message
John Watson
Messages: 4804
Registered: January 2010
Location: Global Village
Senior Member
Your execution plan is unreadable (even with the code tags kindly added by BS). However, it looks as though you are joining row set of only one row to a larger row set, in which case a cartesian join is fine. But without proper column and heading formatting, I can't be sure.
Re: how avoid merge join cartesian. [message #602852 is a reply to message #602611] Mon, 09 December 2013 12:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
na.dharma@gmail.com wrote on Fri, 06 December 2013 10:40

I have a query which is running with merge join
cartesian. can anybody help me to how can i avoid merge join cartesian


Well, you need to provide more information. Post the complete execution plan. A trace would be more helpful. However, a MERGE JOIN CARTESIAN is not always a devil, it depends. You can try using a HASH JOIN hint to force the optimizer to go for a hash join, however, you need to provide a better test case and rich information to go beyond this technical discussion.
Re: how avoid merge join cartesian. [message #602861 is a reply to message #602611] Mon, 09 December 2013 17:31 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
I think you supplied a little to less information.
What does your data look like for instance.
- Cardinality
- indexes (which on what columns)
- RDBMS version may be of influence

Anyway..is the hint syntax you use allright?
I believe the syntax is one of the following:
/*+ index (tab_name index_name) */
/*+ index(my_tab my_tab(col_1, col_2)) */ (since Oracle 10)

But maybe it can also be used as you describe.
Re: how avoid merge join cartesian. [message #603189 is a reply to message #602611] Thu, 12 December 2013 02:10 Go to previous message
michael_bialik
Messages: 605
Registered: July 2006
Senior Member
Hi.

IMHO - In your case MERGE JOIN is the only option:

1. Oracle selects data from A_ADJUSTMENT_AUDIT table using A_ADJ_AUDIT_PK index.
As you didn't supply index DDL's - I assume that the index contains TABLE_NAME and ROW_BRIDGE_KEY columns as it's leading columns.
2. Optimize recognizes that SENSITIVITY_KEY column of F_SENSITIVITY table will always have the same value as ROW_BRIDGE_KEY (because of JOIN), so it uses to apply SKIP SCAN on index F_SENSITIVITY_PK ( it's NOT the leading column of that index).
3. It retrieves a number of rows so as there no additional conditions for JOIN - it adds all these rows to each row selected at first stage by CARTESIAN join.

You can try to avoid that by defining a new index on F_SENSITIVITY with SENSITIVITY_KEY in FIRST place, but I don't expect any performance gains.



HTH.
Previous Topic: how to avoid negative values to number datatype in oracle
Next Topic: SQL Tuning Advisor
Goto Forum:
  


Current Time: Wed Nov 26 22:29:05 CST 2014

Total time taken to generate the page: 0.12092 seconds