oracle performance tuning
add a join_key=1 to both tables then join tables on that key and see the difference between Merge-sort vs Hash-join ---------------------------------------------------------------- -- out 5,6 t1.join_key = t2.join_key AND ( (INSTR(t1.SERVICE_NUMBER, t2.SERVICE_DIGIT) > 0 AND INSTR(t1.SERVICE_PLATFORM, t2.SERVICE_PLATFORM) > 0) /* ( t1.SERVICE_NUMBER like '%' || t2.SERVICE_DIGIT || '%' and t1.SERVICE_PLATFORM like '%' || t2.SERVICE_PLATFORM || '%')*/ OR ( t1.SERVICE_VARIANT_ID= TO_CHAR(t2.SERVICE_VARIANT_ID )AND t1.SDPA_FLAG != 0 AND t1.ABONELIK_FLAG = 'N' ) ) AND T1.DCALL_DATE BETWEEN t2.START_DATE(+) AND t2.END_DATE(+)