Team,
Please find below query and its explain plan ..... I want to re write the query so that hash joins and outer joins should get eliminated from the plan ..... Can some give any idea to me in re writing the query so that it should choose the best plan ..... If I made any mistakes in below query please let me know so that I can correct from my end ......
SELECT DISTINCT OOO.ROW_ID, OOO.PARMA_ID, OOO.VARIABLE_222,
OOO.STATUS_CD, OOO.X_AABR_OPPTY_NAME, SOX.ATTRIB_03,
OOO.PERSON_IDEN, OOO.X_AAB_VARCHAR_06, PSN.ROW_ID,
BBB.X_AAKJS_KO_NUM, UR.LOGIN, OPT1.ROW_ID,
OOO.X_AAB_VARCHAR_03, OOO.DATE_ASSIGN, OOO.SUM_EFFECTIVE_DT,
OOO.DATE_ASSIGN, BBB.X_AABR_CUST_NUMBER, BBB.OU_NUM,
CASE
WHEN BBB.TYPEEYYY = '987'
THEN 'HBJBJHGLJHJHGLJ'
WHEN BBB.TYPEEYYY = '0900'
THEN 'JL HIUHI'
WHEN BBB.TYPEEYYY = '9980'
THEN 'LKLK IIUHIUH'
WHEN BBB.TYPEEYYY = 'KJHKJH'
THEN 'EXT. LOCAL REF.'
ELSE 'BUSS'
END,
CON.PERSON_UID, CON1.ROW_ID, URO.LOGIN, OOO.SECURE_FLG,
CASE
WHEN ATT.PAR_ROW_ID IS NULL
THEN 'N'
ELSE 'Y'
END, OOO.DESC_TEXT, OOO.CREATED, UCR.LOGIN, OOO.LAST_UPD,
UCRR.LOGIN, OOO.CAMP_CON_ID,
CASE
WHEN SOXM.ROW_ID IS NULL
THEN 'N'
ELSE 'Y'
END, OOO.X_AAB_VARCHAR_05, SOE.OU_NUM, SOE1.OU_NUM,
SOE2.OU_NUM, SOX.X_AAB_ABN_ROLE, SOX.X_AAB_OPTY_SECTOR,
SOX.X_AAB_OPTY_TRANSACTION_TYPE, SOX.X_AAB_OPTY_COMPLEXITY,
SOX.X_AAB_OPTY_SIZE, OOO.PEAK_NUM_ATTNDS, OOO.PEAK_NUM_BRK,
SOX.X_AAB_OPTY_CLIENT_SEGMENT, SOX.X_AAB_OPTY_FOCUS,
SOX.X_AAB_VARCHAR_536, OOO.MODIFICATION_NUM, OOO.DB_LAST_UPD,
OOO.DB_LAST_UPD_SRC
FROM SINGLE_OOTY OOO LEFT OUTER JOIN SINGLE_ORIENT BBB
ON BBB.PAR_ROW_ID = OOO.PR_DEPT_OU_ID
LEFT OUTER JOIN SINGLE_PERSON PSN ON PSN.PAR_ROW_ID =
OOO.PR_POSTN_ID
LEFT OUTER JOIN SINGLE_PERSON PT ON PT.PAR_ROW_ID = BBB.PR_POSTN_ID
LEFT OUTER JOIN SINGLE_VERSION URO ON URO.PAR_ROW_ID = PT.PR_EMP_ID
LEFT OUTER JOIN SINGLE_VERSION UR ON UR.PAR_ROW_ID = PSN.PR_EMP_ID
LEFT OUTER JOIN SINGLE_OOTY OPT1 ON OOO.PARMA_ID = OPT1.ROW_ID
LEFT OUTER JOIN SINGLE_CON CON ON CON.PAR_ROW_ID =
OOO.PR_CON_ID
LEFT OUTER JOIN SINGLE_CON CON1 ON CON1.ROW_ID =
OOO.X_AAB_PR_CTP
LEFT OUTER JOIN SINGLE_XMBBER SOXM ON OOO.ROW_ID = SOXM.PAR_ROW_ID
LEFT OUTER JOIN SINGLE_ORION SOX ON OOO.ROW_ID = SOX.PAR_ROW_ID
LEFT OUTER JOIN SINGLE_ORIENT SOE
ON SOE.PAR_ROW_ID = SOX.X_AAB_SELLER_ID
LEFT OUTER JOIN SINGLE_ORIENT SOE1
ON SOE1.PAR_ROW_ID = SOX.X_AAB_BUYER_ID
LEFT OUTER JOIN SINGLE_ORIENT SOE2
ON SOE2.PAR_ROW_ID = SOX.X_AAB_OTHER_ID
LEFT OUTER JOIN SINGLE_VERSION UCR ON UCR.ROW_ID = OOO.CREATED_BY
LEFT OUTER JOIN SINGLE_VERSION UCRR ON UCRR.ROW_ID = OOO.LAST_UPD_BY
LEFT OUTER JOIN SINGLE_ORIENT ORG1
ON ORG1.PR_POSTN_ID = PT.ROW_ID
AND ORG1.PAR_ROW_ID = OOO.PR_DEPT_OU_ID
LEFT OUTER JOIN SINGLE_ERRAT ATT ON ATT.PAR_ROW_ID = OOO.ROW_ID
WHERE OOO.CONSUMER_OPTY_FLG = 'N'
ORDER BY OOO.ROW_ID;
PLAN_TABLE_OUTPUT
PLAN HASH VALUE: 4228047222
----------------------------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES |TEMPSPC| COST (%CPU)| TIME |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8040K| 2982M| | 5008K (1)| 16:41:37 |
| 1 | SORT UNIQUE | | 8040K| 2982M| 3140M| 5008K (1)| 16:41:37 |
| 2 | MERGE JOIN OUTER | | 8040K| 2982M| | 4342K (1)| 14:28:32 |
| 3 | SORT JOIN | | 8040K| 2898M| 5982M| 4306K (1)| 14:21:13 |
|* 4 | HASH JOIN RIGHT OUTER | | 8040K| 2898M| 150M| 3658K (1)| 12:11:47 |
| 5 | TABLE ACCESS FULL | SINGLE_ORIENT | 4638K| 97M| | 152K (1)| 00:30:26 |
| 6 | VIEW | | 8040K| 2729M| | 3359K (1)| 11:11:53 |
|* 7 | HASH JOIN RIGHT OUTER | | 8040K| 4386M| 1800K| 3359K (1)| 11:11:53 |
| 8 | TABLE ACCESS FULL | SINGLE_VERSION | 63433 | 1053K| | 343 (1)| 00:00:05 |
|* 9 | HASH JOIN RIGHT OUTER | | 7828K| 4143M| 3656K| 3149K (1)| 10:29:50 |
| 10 | TABLE ACCESS FULL | SINGLE_PERSON | 91229 | 2583K| | 719 (1)| 00:00:09 |
|* 11 | HASH JOIN RIGHT OUTER | | 7828K| 3926M| 300M| 2949K (1)| 09:49:49 |
| 12 | TABLE ACCESS FULL | SINGLE_ORIENT | 4638K| 247M| | 152K (1)| 00:30:28 |
|* 13 | HASH JOIN RIGHT OUTER | | 7828K| 3508M| 1800K| 2603K (1)| 08:40:42 |
| 14 | TABLE ACCESS FULL | SINGLE_VERSION | 63433 | 1053K| | 343 (1)| 00:00:05 |
|* 15 | HASH JOIN RIGHT OUTER | | 7621K| 3292M| 3656K| 2435K (1)| 08:07:06 |
| 16 | TABLE ACCESS FULL | SINGLE_PERSON | 91229 | 2583K| | 719 (1)| 00:00:09 |
|* 17 | HASH JOIN RIGHT OUTER | | 7556K| 3055M| 348M| 2278K (1)| 07:35:45 |
| 18 | TABLE ACCESS FULL | SINGLE_CON | 10M| 225M| | 446K (1)| 01:29:22 |
|* 19 | HASH JOIN RIGHT OUTER | | 7556K| 2897M| | 1666K (1)| 05:33:21 |
| 20 | TABLE ACCESS FULL | SINGLE_XMBBER | 7269 | 156K| | 68 (0)| 00:00:01 |
|* 21 | HASH JOIN RIGHT OUTER | | 7556K| 2738M| 159M| 1666K (1)| 05:33:20 |
| 22 | TABLE ACCESS FULL | SINGLE_ORIENT | 4638K| 106M| | 151K (1)| 00:30:24 |
|* 23 | HASH JOIN RIGHT OUTER | | 7556K| 2565M| 159M| 1375K (1)| 04:35:04 |
| 24 | TABLE ACCESS FULL | SINGLE_ORIENT | 4638K| 106M| | 151K (1)| 00:30:24 |
|* 25 | HASH JOIN RIGHT OUTER | | 7556K| 2392M| 159M| 1092K (1)| 03:38:31 |
| 26 | TABLE ACCESS FULL | SINGLE_ORIENT | 4638K| 106M| | 151K (1)| 00:30:24 |
|* 27 | HASH JOIN RIGHT OUTER | | 7556K| 2219M| 425M| 818K (1)| 02:43:40 |
| 28 | TABLE ACCESS FULL | SINGLE_ORION | 8750K| 325M| | 135K (2)| 00:27:08 |
|* 29 | HASH JOIN RIGHT OUTER | | 7429K| 1905M| 246M| 562K (1)| 01:52:35 |
| 30 | INDEX FAST FULL SCAN | SYS_C005892 | 10M| 123M| | 7597 (1)| 00:01:32 |
|* 31 | HASH JOIN RIGHT OUTER | | 7429K| 1820M| 1800K| 448K (1)| 01:29:42 |
| 32 | TABLE ACCESS FULL | SINGLE_VERSION | 63433 | 1053K| | 343 (1)| 00:00:05 |
|* 33 | HASH JOIN RIGHT OUTER | | 7429K| 1700M| 1800K| 359K (1)| 01:11:55 |
| 34 | TABLE ACCESS FULL | SINGLE_VERSION | 63433 | 1053K| | 343 (1)| 00:00:05 |
|* 35 | HASH JOIN RIGHT OUTER| | 7429K| 1580M| 164M| 276K (1)| 00:55:18 |
| 36 | INDEX FAST FULL SCAN| SYS_C006434 | 7844K| 74M| | 5470 (1)| 00:01:06 |
|* 37 | TABLE ACCESS FULL | SINGLE_OOTY | 7429K| 1509M| | 183K (1)| 00:36:46 |
|* 38 | SORT JOIN | | 2990K| 31M| 114M| 36616 (1)| 00:07:20 |
| 39 | TABLE ACCESS FULL | SINGLE_ERRAT | 2990K| 31M| | 23530 (1)| 00:04:43 |
----------------------------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
4 - ACCESS("ORG1"."PAR_ROW_ID"(+)="OOO"."PR_DEPT_OU_ID" AND "ORG1"."PR_POSTN_ID"(+)="PT"."ROW_ID")
7 - ACCESS("URO"."PAR_ROW_ID"(+)="PT"."PR_EMP_ID")
9 - ACCESS("PT"."PAR_ROW_ID"(+)="BBB"."PR_POSTN_ID")
11 - ACCESS("BBB"."PAR_ROW_ID"(+)="OOO"."PR_DEPT_OU_ID")
13 - ACCESS("UR"."PAR_ROW_ID"(+)="PSN"."PR_EMP_ID")
15 - ACCESS("PSN"."PAR_ROW_ID"(+)="OOO"."PR_POSTN_ID")
17 - ACCESS("CON"."PAR_ROW_ID"(+)="OOO"."PR_CON_ID")
19 - ACCESS("OOO"."ROW_ID"="SOXM"."PAR_ROW_ID"(+))
21 - ACCESS("SOE"."PAR_ROW_ID"(+)="SOX"."X_AAB_SELLER_ID")
23 - ACCESS("SOE1"."PAR_ROW_ID"(+)="SOX"."X_AAB_BUYER_ID")
25 - ACCESS("SOE2"."PAR_ROW_ID"(+)="SOX"."X_AAB_OTHER_ID")
27 - ACCESS("OOO"."ROW_ID"="SOX"."PAR_ROW_ID"(+))
29 - ACCESS("CON1"."ROW_ID"(+)="OOO"."X_AAB_PR_CTP")
31 - ACCESS("UCR"."ROW_ID"(+)="OOO"."CREATED_BY")
33 - ACCESS("UCRR"."ROW_ID"(+)="OOO"."LAST_UPD_BY")
35 - ACCESS("OOO"."PARMA_ID"="OPT1"."ROW_ID"(+))
37 - FILTER("OOO"."CONSUMER_OPTY_FLG"='N')
38 - ACCESS("ATT"."PAR_ROW_ID"(+)="FROM$_SUBQUERY$_031"."QCSJ_C000000000300000")
FILTER("ATT"."PAR_ROW_ID"(+)="FROM$_SUBQUERY$_031"."QCSJ_C000000000300000")