We have a table with about 10 millions of rows and we need to delete a large number of rows (about 1 million) with a delete statement like



We are doing this (using Spring) via



but the delete still takes much too long (a few minutes).

Doing some research in DB forums the reason seems to be the DB transaction log which would become very big with such a mass delete statement. Their advice is to split the mass delete into many smaller transactions. Is there any way to let Hibernate do so automatically or do I have to write my own programmatic transactions? Till now I use transactions by annotation.

Thanks in advance,
Ole

As this problem has popped up again I'd like to kindly ask for any help again.

Did you trace the statement that is being executed? Is there an index on the column "someid"? Is your DB perfoming a full table scan or does it use some other index that's not suitable you that statement? What does EXPLAIN PLAN (or whatever you DB uses) tell you about the statement?
Is there only one statement or does the spring template execute more statements?

Yes, the statement itself is fast.


There is an index for that column.


As there is an index for the column that one is used.


That the statement is fast.


It is only one statement.

The problem is not the statement itself. It is very slow because of the transaction log that has to be written for 1 million rows.