Как используя PreparedStatement удалить все записи из БД, задавая batchSize?

Question

Как используя PreparedStatement удалить все записи из БД, задавая batchSize?

В таблице около миллиона записей, нужно их удалить, используя PreparedStatement и задавать при этом batchSize. Чтобы удаление происходило частями, например, по 1000 записей, если задали batchSize = 1000;.

Запрос такого вида :

delete BpmsExtraAttributeEntityImpl EA inner join 
BpmsHistoricTaskInstanceEntityImpl HTI on EA.taskId = HTI.id inner 
join BpmsHistoricProcessInstanceEntityImpl HPI on 
HTI.processInstanceId = HPI.id where HPI.endTime is null

Answer 1

Если удалить нужно все записи (без условий), то гораздо эффективнее воспользоваться командой:

TRUNCATE TABLE table_name;

это удаление на уровне метаданных и скорость удаления обычно не зависит от размера таблицы.

Answer 2

Для MySQL:

CREATE PROCEDURE delete_by_chunks (tablename VARCHAR(64), chunk_size INT)
BEGIN
    SET @sql := CONCAT( 'DELETE FROM ',
                        tablename,
                        ' ORDER BY id LIMIT ',
                        chunk_size );
    PREPARE stmt FROM @sql;
    REPEAT
        EXECUTE stmt;
    UNTIL ROW_COUNT() < chunk_size END REPEAT;
    DROP PREPARE stmt;
END

fiddle

Запрос на удаление может быть любой сложности. Только его надо аккуратно собрать. Но непременно однотабличный - для многотабличного запроса на удаление метод не сработает, ибо там не предусмотрен LIMIT... так что из остальных таблиц данные должны удаляться каскадно по внешним ключам.

PS. Хотя fiddle работает только на версии 8+ (лень мне генерить набор данных руками), код процедуры применим и к 5+.

Answer 3

Для удаления всех записей из таблицы использовать DELETE не целесообразно и не рекомендуется. Тем более удалять записи по частям.

Гораздо эффективнее воспользоваться командой TRUNCATE.
В этом случае, использовать PreparedStatement нет смысла, так как за отсутствием подстановочных переменных подготавливать не для чего.

Попробуйте так:

create or replace and compile java source named TestSqlExecution as
import java.sql.*;
public class TestSqlExecution {
    public static void cleanseTable (String name) {
        try {
            Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbsrv:1521/pdb1","user","pass");
            Statement stmt = conn.createStatement ();
            stmt.execute ("truncate table " + name);
            System.out.println ("cleanseTable(" + name + ") executed successfully.");
        } catch (Exception e) { e.printStackTrace(); }
    }
}
/
create or replace procedure cleanseTable (name varchar2) as
    language java name 'TestSqlExecution.cleanseTable (java.lang.String)';
/

Тестовые данные и запуск:

create table tab1 as 
    select rownum id from dual connect by level <= 10e5;

exec cleanseTable ('tab1')

cleanseTable(tab1) executed successfully.

БЛОГ НА HUSL

Как используя PreparedStatement удалить все записи из БД, задавая batchSize?

Ответы (3 шт):