A very complex query running long
Here is the Query and also attaching explain plan.
The database is standard edition and running on 10.2.0.4.
optimizer_mode string ALL_ROWS
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
INSERT /*+ APPEND */
INTO IFACS_SUMMARY_WORK (FVO,
CLIENTID,
SUBSIDIARY,
PLATESOURCE,
IA,
IANAME,
IARESST,
IARESCNTRY,
IAST,
IACNTRY,
IAWEBSITE,
IATYPE,
IATYPEATRESP,
IADEF,
VIOID,
VIONUM,
PENALTY,
SUSPEND,
VIOAMT,
CALCAMT,
VIOCURR,
APT,
CALCDAYS,
VIODT,
VIOTIME,
VIOPLATE,
VIOPLATEST,
VIOTYPE,
VIODUEDT,
VIONOTICEDT,
VIOAFFDUEDT,
VIOAFFDUEDTSTARTDT,
VIOKEYEDDUEDT,
VIOAFFDUEDTCALC,
MAILRCVDDT,
KEYDT,
RELDT,
REQDT,
RESPDT,
LASTRESPERRDT,
DOCNUM,
BATCHTYPE,
BATCHPROCTYPE,
FILENAMEUPLD,
FILENAMEEVIO,
CLIENTRESP,
STATRESPPARTY,
TYPERESPPARTY,
FINLIAB,
FINLIABREASON,
CLIENTFINLIABEFFDT,
RNTFINBILLEDTOCLIENTDT,
FINREIMBURSETOCLIENTDT,
CENTRALBILLING,
TYPERESPPARTYCHGDT,
RESPREASON,
SOLDSTOLENDT,
SALETYPE,
RESPTYPE,
BPADT,
CCNUMPROV,
CCCREDITORDEBIT,
CCEXPDT,
CCADDDT,