HI Experts,
We need to create TEMP TABLE WILL HAVE records older than 90 days and STATUS SENT from J_DOC table.
create table TEMP_ID_STAT_TIME_FRM_JOB_DOC as select JOB_ID, last_update_time_utc, status from J_DOC where
LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status='Sent';
total records are: 4659140
select count(*) from TEMP_ID_STAT_TIME_FRM_JOB_DOC;
4659140
so we have created index on JOB_ID column to perform deletion operations faster any suggestion for faster performance:-
CREATE INDEX JOB_ID_INDEX
ON TEMP_ID_STAT_TIME_FRM_JOB_DOC(JOB_ID);
CREATE INDEX JOB_ID_INDEX_JOB_DOC
ON J_DOC(JOB_ID);
CREATE INDEX JOB_ID_INDEX_HUB_SIG
ON HUB_SIG(JOB_ID);
We need to first delete from hub_sig table for records older than 90 days and STATUS SENT
delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC);
after that we need to delete from main table J_DOC table
delete from J_DOC
where JOB_id IN ( SELECT JOB_ID
FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC);
Please suggest for faster deletion operation what we can include, also once that day job is completed
we need to rename this temp table to TEMP_JOB_ID_FROM_J_DOC_1119
we need to include all these steps in procedure for cleanup of older data 90 days in different servers , kindly suggest.