Oracle Delete query optimization

Question

DELETE FROM   BATCH_T   WHERE   BatchId   In (  BatchIdList  )    AND   Status   = 'CLOSED'

The above query is taking 3 mins to finish. Can anybody help me to reduce the response time.

Tim Biegeleisen · Accepted Answer · 2020-03-16 07:25:01Z

1

If the batch list be small, then consider adding the following index to your table:

CREATE INDEX batch_idx ON BATCH_T (BatchId, Status);

If you have a very long batch list, then consider adding those values to a new table BATCH_LIST. Then, make sure that the following index exists on this new table:

CREATE INDEX other_idx ON BATCH_LIST (BatchId);

The first index should help either version.

answered Mar 16, 2020 at 7:25

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gangs165700 Over a year ago

Indexes are created on status ,batchId columns already. basically i am calling above query from java program using jdbcTemplate.

Tim Biegeleisen Over a year ago

Then check the execution plan to whether mine (your) indices are being used.

Collectives™ on Stack Overflow

Oracle Delete query optimization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related