MVW Refresh takes hours (and fails) but Select statement takes 15 minutes

Hello

We have a materialized for that takes just 15 minutes to create but when it refreshes it takes hours and usually rollsback with snapshot too old.

As part of the refresh, indexes are dropped but when it comes to the refresh itself, the INSERT INTO SELECT statement is different to that of the SELECT statement in the CREATE MVW.

E.G. This is the create statement....

CREATE MATERIALIZED VIEW COSMOS_REPORTS.DIS_SHIPMENT_MED_NUMBERS_MVW (MEDICATION_NUMBER,LOT_NUMBER,SUBINVENTORY_CODE,PATIENT_NUMBER,VISIT_NUMBER,SERIAL_STATE,CONTAINER_NUMBER,DISTRIBUTION_ORDER_NO,USER_STATUS,SHIPPED_FLAG,INVENTORY_ITEM_ID,ORGANIZATION_ID,LINE_ID,HEADER_ID,TRIAL_SITE_ID,SHIP_TO_CONTACT_ID,CURRENT_ORGANIZATION_ID,STATE_CODE,TRANSACTION_ID)