We have a job which has around 10 inserts into table X. Out of the 10 inserts one particilar insert takes around 30 minutes and the rest together takes 30 minutes. Total execution time of the job is 1 hr.
If we split the job into two, the query which takes more query in JOB1 and the rest in JOB2, will the execution time get reduced?
I have read that the table is locked in exclusive mode during insert. If so then JOB2 has to wait till for JOB1 to finish. Is it true?
How many rows does this rows have?
How many indexes does this table have?
Are other applications reading this table while inserts are going on, if so do those applicatoins perform commits at regular interval.
If table you are inserting is a partioned table then you can create jobs seperated by partioning key ( or a set of partioning keys). Sort/distribute data by same partioning key.
If you go with above suggested way, also check application if it has any other statements, SELECT, DELETE and these better be using whole clustering index key in WHERE clause. Otherwise split jobs will be contending with each other.