After The Addition Of Guarantor Data, The View HCD.V_PATIENT_ACCOUNT_F_INCR Performs Poorly (Doc ID 2803552.1)

Oracle Healthcare Foundation - Version 8.0.1 and later

ACTUAL BEHAVIOR
---------------
After the addition of guarantor data the view "HCD.V_PATIENT_ACCOUNT_F_INCR" performs poorly.

believe issue is the clause AND EXISTS (SELECT 1 FROM INC_ID_WITH WHERE HDM_PT_ACCT_GUARNTR.PT_ACCT_ID = INC_ID_WITH.PT_ACCT_ID) in the view below which causes

| 76 | STATISTICS COLLECTOR | | | | | | | | | Q7,02 | PCWC | |
| 77 | MERGE JOIN CARTESIAN | | 822K| 42M| | 4408 (2)| 00:00:01 | | | Q7,02 | PCWP | |
| 78 | VIEW | | 4687K| 58M| | 1536 (1)| 00:00:01 | | | Q7,02 | PCWP | |

GAURANTOR_WITH AS
  (SELECT /*+ materialize */ *
  FROM
  (SELECT W_HCD_GUARANTOR_D.row_wid,
  HDM_PT_ACCT_GUARNTR.PT_ACCT_ID,
  ROW_NUMBER() OVER( PARTITION BY HDM_PT_ACCT_GUARNTR.PT_ACCT_ID ORDER BY NVL(HDM_PT_ACCT_GUARNTR.SEQ_NBR,999999) ASC, NVL(HDM_PT_ACCT_GUARNTR.STRT_DT,to_date('01-JAN-0001 23:59:59','DD-MON-YYYY HH24:MI:SS')) DESC, HDM_PT_ACCT_GUARNTR.EFFECTIVE_FROM_DT DESC) AS SRLNO
  FROM HDM_PT_ACCT_GUARNTR,
  HDM_GUARNTR,
  W_HCD_GUARANTOR_D
  WHERE HDM_PT_ACCT_GUARNTR.CURRENT_FLG ='Y'
  AND HDM_GUARNTR.CURRENT_FLG ='Y'
  AND HDM_PT_ACCT_GUARNTR.delete_flg='N'
  AND HDM_GUARNTR.GUARNTR_ID =HDM_PT_ACCT_GUARNTR.GUARNTR_ID
  AND HDM_GUARNTR.INTEGRATION_ID =W_HCD_GUARANTOR_D.INTEGRATION_ID
  AND HDM_GUARNTR.DATASOURCE_NUM_ID =W_HCD_GUARANTOR_D.DATASOURCE_NUM_ID
  AND EXISTS (SELECT 1 FROM INC_ID_WITH WHERE HDM_PT_ACCT_GUARNTR.PT_ACCT_ID = INC_ID_WITH.PT_ACCT_ID)
  ) PIVOT (MIN(ROW_WID) ROW_WID FOR srlno IN (1 AS R1,2 AS R2))
  ),


EXPECTED BEHAVIOR
-----------------------
Expect to perform faster.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Added guarantor data to view w_hcd_patient_account_f_incr
2. Run incremental load
3. Slow performance observed.