Hi Oracle Guru's,
I have staging table which is getting updated by an informatica jobs and it results in a deadlock. Below are the update statements executed by the batch job.
Update Statement message :
Job1
update STG_AUDIT_RPT
set sts_cd='reported'
where rpt_nme = 'FIN_CLAIMS'
and sts_cd ='loaded';
Job2
update STG_AUDIT_RPT
set sts_cd='reported'
where rpt_nme = 'FIN_CLOSE'
and sts_cd ='loaded';
Create Table Statement
CREATE TABLE "STG_AUDIT_RPT"
( "ID" NUMBER NOT NULL ENABLE,
"RPT_NME" VARCHAR2(20 CHAR) NOT NULL ENABLE,
"STS_CD" VARCHAR2(20 CHAR) NOT NULL ENABLE,
"JSON_RECRD" VARCHAR2(4000 CHAR) NOT NULL ENABLE,
"ORD_NBR" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"CLM_ID" NUMBER(*,0),
"TRAN_ID" NUMBER(*,0),
"HANDLED_DTM" TIMESTAMP (6) NOT NULL ENABLE,
"CREATE_DATE" TIMESTAMP (6) DEFAULT SYSDATE,
"CREATE_USER_ID" VARCHAR2(20 CHAR),
"LAST_MOD_DATE" TIMESTAMP (6) DEFAULT SYSDATE,
"LAST_MOD_USER" VARCHAR2(20 CHAR),
CONSTRAINT "STG_AUDIT_RPT_PK" PRIMARY KEY ("ID")
)
I have non-unique index defined on RPT_NME, STS_CD, CREATE_DATE (individually).
One of the option is getting the batch jobs to run in sequence, but it will result in more batch processing time.
Need you expertise in resolving the dead lock, will adding a composite index (rpt_nme and sts_cd) can resolve the dead lock.
Thank you for all your time in advance.