Oracle Database Discussions

Hi,

we have a query which is hanging for more than 3 hrs , merge join cartesian is found in the plan. please let me know how to avoid that.

Query:
SELECT
REP_V_CAMPAIGN.NAME,
ALIAS_ACCOUNT_MANAGER.NAME,
ALAIS_CONTACT_ACCOUNT.CHANNEL,
ALIAS_ACCOUNT_MANAGER_WRKGROUP.WORKGROUP
FROM
REP_V_CAMPAIGN,
REP_V_EMPLOYEE ALIAS_ACCOUNT_MANAGER,
REP_V_ACCOUNT ALAIS_CONTACT_ACCOUNT,
REP_V_WORKGROUP ALIAS_ACCOUNT_MANAGER_WRKGROUP,
REP_V_CAMPAIGN_CONTACT,
REP_V_CAMPAIGN_CONTACT_MAPPING
WHERE
( REP_V_CAMPAIGN.CAMPAIGN_ID=REP_V_CAMPAIGN_CONTACT_MAPPING.CAMPAIGN_ID )
AND ( REP_V_CAMPAIGN_CONTACT.CONTACT_ID=REP_V_CAMPAIGN_CONTACT_MAPPING.CONTACT_ID )
AND ( ALAIS_CONTACT_ACCOUNT.ACCOUNT_ID=REP_V_CAMPAIGN_CONTACT.ACCOUNT_ID )
AND ( ALAIS_CONTACT_ACCOUNT.MANAGER_ID=ALIAS_ACCOUNT_MANAGER.EMPLOYEE_ID )
AND ( ALIAS_ACCOUNT_MANAGER.WORKGROUP_ID=ALIAS_ACCOUNT_MANAGER_WRKGROUP.WORKGROUP_ID )
AND (
REP_V_CAMPAIGN.NAME IN ('BD-GVA-2005-04-Global Phone Day')
)


Plan:

SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 463 | 28424 (1)|
| 1 | NESTED LOOPS | | 1 | 463 | 28424 (1)|
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS OUTER | | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 422 | 28422 (1)|
| 5 | NESTED LOOPS OUTER | | 1 | 373 | 21636 (1)|
| 6 | NESTED LOOPS OUTER | | 1 | 364 | 21635 (1)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS OUTER | | 1 | 355 | 21634 (1)|
| 8 | NESTED LOOPS OUTER | | 1 | 346 | 21633 (1)|
| 9 | NESTED LOOPS | | 1 | 337 | 21632 (1)|
| 10 | NESTED LOOPS | | 1 | 282 | 21621 (1)|
| 11 | MERGE JOIN CARTESIAN | | 9946K| 2286M| 21620 (1)|
| 12 | NESTED LOOPS OUTER | | 5001 | 1108K| 1616 (1)|
| 13 | NESTED LOOPS OUTER | | 5001 | 1064K| 1615 (1)|
| 14 | NESTED LOOPS | | 5001 | 1020K| 1614 (1)|
| 15 | NESTED LOOPS OUTER | | 11 | 1947 | 19 (6)|
| 16 | NESTED LOOPS OUTER | | 11 | 1837 | 18 (6)|
| 17 | NESTED LOOPS OUTER | | 11 | 1738 | 17 (6)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 18 | NESTED LOOPS OUTER | | 11 | 1639 | 16 (7)|
|* 19 | TABLE ACCESS BY INDEX ROWID| S_SRC | 11 | 1595 | 15 (7)|
|* 20 | INDEX RANGE SCAN | S_SRC_M7 | 37 | | 3 (0)|
|* 21 | INDEX RANGE SCAN | S_LANG_U2 | 1 | 4 | |
|* 22 | INDEX UNIQUE SCAN | S_BU_P1 | 1 | 9 | |
|* 23 | INDEX UNIQUE SCAN | S_BU_P1 | 1 | 9 | |
|* 24 | INDEX UNIQUE SCAN | S_NOTE_P1 | 1 | 10 | |
| 25 | TABLE ACCESS BY INDEX ROWID | S_CAMP_CON | 459 | 14688 | 146 (1)|
|* 26 | INDEX RANGE SCAN | S_CAMP_CON_M1 | 459 | | 1 (0)|
|* 27 | INDEX UNIQUE SCAN | S_BU_P1 | 1 | 9 | |
|* 28 | INDEX UNIQUE SCAN | S_POSTN_P1 | 1 | 9 | |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 29 | BUFFER SORT | | 1989 | 27846 | 21618 (0)|
|* 30 | TABLE ACCESS FULL | S_BU | 1989 | 27846 | 4 (0)|
|* 31 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 41 | 2 (50)|
|* 32 | INDEX RANGE SCAN | S_ORG_EXT_F13 | 3340 | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | S_CONTACT | 1 | 55 | 12 (9)|
|* 34 | INDEX RANGE SCAN | S_CONTACT_M13 | 21 | | 1 (0)|
|* 35 | INDEX UNIQUE SCAN | PK_SM | 1 | 9 | |
|* 36 | INDEX UNIQUE SCAN | S_EMP_PER_P1 | 1 | 9 | |
|* 37 | INDEX UNIQUE SCAN | S_USER_P1 | 1 | 9 | |
|* 38 | INDEX UNIQUE SCAN | S_POSTN_P1 | 1 | 9 | |
| 39 | BUFFER SORT | | 436K| 20M| 28420 (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 40 | TABLE ACCESS FULL | S_ORG_EXT | 436K| 20M| 6786 (0)|
| 41 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 19 | 2 (50)|
|* 42 | INDEX UNIQUE SCAN | S_POSTN_P1 | 1 | | |
|* 43 | VIEW | V_CAMPAIGN_CONTACT | 1 | 22 | |
| 44 | UNION-ALL PARTITION | | | | |
| 45 | NESTED LOOPS OUTER | | 1 | 64 | 4 (0)|
| 46 | NESTED LOOPS OUTER | | 1 | 54 | 3 (0)|
|* 47 | TABLE ACCESS BY INDEX ROWID | S_CONTACT | 1 | 44 | 2 (0)|
|* 48 | INDEX UNIQUE SCAN | S_CONTACT_P1 | 1108K| | 2 (0)|
|* 49 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | 10 | |
|* 50 | INDEX UNIQUE SCAN | S_ORG_EXT_P1 | 1 | 10 | |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 51 | INDEX UNIQUE SCAN | S_PRSP_CONTACT_P1 | 1 | 10 | 2 (0)|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("SPST"."PR_EMP_ID"="SCNT"."ROW_ID")
19 - filter("S"."SRC_CD"='Marketing Campaign' AND "S"."CAMP_TYPE_CD" IS NULL)
20 - access("S"."NAME"='BD-GVA-2005-04-Global Phone Day')
21 - access("S"."PREF_LANG_ID"="L"."LANG_CD"(+))
22 - access("S"."PR_PRTNR_ID"="B1"."ROW_ID"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
23 - access("S"."BU_ID"="B2"."ROW_ID"(+))
24 - access("S"."PR_D_NOTE_ID"="N"."ROW_ID"(+))
26 - access("S"."ROW_ID"="S"."SRC_ID")
27 - access("S"."BU_ID"="B"."ROW_ID"(+))
28 - access("S"."POSTN_ID"="P"."ROW_ID"(+))
30 - filter("B"."BU_FLG"='Y')
31 - filter("S"."INT_ORG_FLG"='Y')
32 - access("S"."PAR_BU_ID"="B"."ROW_ID")
filter("S"."PAR_BU_ID" IS NOT NULL)
33 - filter("SCNT"."EMP_FLG"='Y')
34 - access("SCNT"."PR_DEPT_OU_ID"="S"."PAR_ROW_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
35 - access("SCNT"."ROW_ID"="E"."EMPLOYEE_ID"(+))
36 - access("SCNT"."ROW_ID"="SEMP"."ROW_ID"(+))
37 - access("SCNT"."ROW_ID"="SUSR"."ROW_ID"(+))
38 - access("SCNT"."PR_HELD_POSTN_ID"="SPST"."ROW_ID"(+))
40 - filter(("SORG"."INT_ORG_FLG"<>'Y' OR "SORG"."PRTNR_FLG"<>'N') AND "SORG"."ACCNT_FLG"<>'N')
42 - access("SORG"."PR_POSTN_ID"="SPST"."ROW_ID"(+))
43 - filter("SORG"."ROW_ID"="REP_V_CAMPAIGN_CONTACT"."ACCOUNT_ID")
47 - filter("SCNT"."PRIV_FLG"='N' AND "SCNT"."EMP_FLG"='N')
48 - access("SCNT"."ROW_ID"=DECODE("S"."CON_PER_ID",NULL,"S"."PRSP_CON_PER_ID","S"."CON_PER_ID"))
49 - access("SCNT"."PR_OU_ADDR_ID"="SADP"."ROW_ID"(+))
50 - access("SCNT"."PR_DEPT_OU_ID"="SORG"."ROW_ID"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
51 - access("SPRP"."ROW_ID"=DECODE("S"."CON_PER_ID",NULL,"S"."PRSP_CON_PER_ID","S"."CON_PER_ID"))

89 rows selected.

SQL> SPOOL OFF