HI Friends,
The below query is run by a developer. Please cna you help me re-write this? This is running to slow and doing a lot of full table scans
SELECT a.id_member,
a.code_hotel
FROM tmp_enroll_cur_mth a
LEFT OUTER JOIN tmp_enroll_pri_mth b
ON (a.first_name = b.first_name
AND a.last_name = b.last_name
AND (a.email_addr = b.email_addr
OR a.code_postal = b.code_postal))
;
and the explain plan for it is
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2907925857
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1711K| 226M| 1382M (8)|999:59:59 |
| 1 | NESTED LOOPS OUTER | | 1711K| 226M| 1382M (8)|999:59:59 |
| 2 | TABLE ACCESS FULL | TMP_ENROLL_CUR_MTH | 77806 | 10M| 189 (5)| 00:00:02 |
| 3 | VIEW | | 22 | | 17772 (8)| 00:02:46 |
|* 4 | TABLE ACCESS FULL| TMP_ENROLL_PRI_MTH | 22 | 2574 | 17772 (8)| 00:02:46 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."FIRST_NAME"="B"."FIRST_NAME" AND
"A"."LAST_NAME"="B"."LAST_NAME" AND ("A"."EMAIL_ADDR"="B"."EMAIL_ADDR" OR
"A"."CODE_POSTAL"="B"."CODE_POSTAL"))
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
Thx!