spring batch meta table data delete

배치를 돌리다보면 spring batch meta table 에 데이터가 쌓인다.

 

많은 job을 돌릴수록 데이터가 많이 쌓여 있어 삭제 가 필요 할수 있다.

 

그때 사용할수 있는 delete 쿼리로 순서대로 삭제 하는 쿼리 가 있는데

 

 
DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN ( SELECT STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION WHERE START_TIME < '20230224' );
 
DELETE FROM BATCH_STEP_EXECUTION WHERE START_TIME < '20230224';
 
DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN ( SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE CREATE_TIME < '20230224');
 
DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN ( SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE CREATE_TIME < '20230224');
 
DELETE FROM BATCH_JOB_EXECUTION WHERE CREATE_TIME < '20230224';
 
DELETE FROM BATCH_JOB_INSTANCE A WHERE NOT EXISTS ( SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION B WHERE A.JOB_INSTANCE_ID = B.JOB_INSTANCE_ID );

이런식이다

배치로 삭제 돌리다보면 많은 데이터가 있을때 BATCH_JOB_EXECUTION ,BATCH_JOB_INSTANCE  

이 두 테이블의 삭제 속도가 많이 느려진다.

 

그럴때 

 

CREATE INDEX BATCH_STEP_EXECUTION_IDX ON BATCH_STEP_EXECUTION (JOB_EXECUTION_ID);
CREATE INDEX BATCH_JOB_EXECUTION_PARAMS_IDX ON BATCH_JOB_EXECUTION_PARAMS (JOB_EXECUTION_ID);
CREATE INDEX BATCH_JOB_EXECUTION_IDX ON BATCH_JOB_EXECUTION (JOB_INSTANCE_ID);
 
-- 이테이블은 생성시 index 걸리는데 안걸려있으면 
CREATE INDEX BATCH_JOB_EXECUTION_CONTEXT_IDX ON BATCH_JOB_EXECUTION_CONTEXT (JOB_EXECUTION_ID);

이런식으로 index 생성하면 속도 향상 된다.