Oracle Database Discussions

Hello,

The below select statement is taking more than 2 hours to complete.

SELECT wins_pol_no, vrsn_no, pol_typ, pol_eff_dt, pol_bus_no, row_eff_ts,
pol_exp_dt, row_exp_ts, row_ins_ts, row_updt_ts, row_updt_sys_id,
row_updt_usr_id, curr_row_ind, insrd_state_cd, srce_cmpny_cd
FROM infodev.wins_policy wp
WHERE EXISTS (
SELECT 1
FROM infodev.v_wins_policy_ext wpd
WHERE wp.pol_bus_no = wpd.pol_bus_no
AND wp.pol_eff_dt = wpd.pol_eff_dt
AND wp.pol_exp_dt = wpd.prodct_exp_dt);

explain plan:

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

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9828 | 873K| 157K (0)|
| 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | WINS_POLICY | 9828 | 873K| 205 (0)|
| 3 | FILTER | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | WINS_POLICY_DRV | 1 | 32 | 2 (50)|
| 5 | NESTED LOOPS | | 1 | 57 | 14 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| INS_RQMT_PRODCT | 1 | 25 | 13 (0)|
| 7 | INDEX RANGE SCAN | INS_RQMT_PRODCT_XI_EFF_DT | 278 | | 3 (0)|
| 8 | INDEX RANGE SCAN | FIELDF | 1 | | |
| 9 | TABLE ACCESS FULL | POL_INFO_XREF | 1 | 3 | 2 (0)|
---------------------------------------------------------------------------------------------------

Note: PLAN_TABLE' is old version


Please help to minimize the execution time.

Regards,
Ashish