Код IT
← Каталог

Шпаргалка с типичными задачами по SQL — Разбиение большого запроса на части

Фрагмент из «Шпаргалка с типичными задачами по SQL»: Разбиение большого запроса на части.

sql sqlencyclopedia885 embed URL статья в энциклопедии
SQL main.sql 
-- Обработка данными порциями
DO $$
DECLARE
    batch_size integer := 10000;
    processed integer := 1;
BEGIN
    WHILE processed > 0 LOOP
        WITH deleted AS (
            DELETE FROM large_log_table
            WHERE id IN (
                SELECT id FROM large_log_table LIMIT batch_size
            )
            RETURNING id
        )
        SELECT COUNT(*) INTO processed FROM deleted;
        
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;
-- Обработка данными порциями
DO $$
DECLARE
    batch_size integer := 10000;
    processed integer := 1;
BEGIN
    WHILE processed > 0 LOOP
        WITH deleted AS (
            DELETE FROM large_log_table
            WHERE id IN (
                SELECT id FROM large_log_table LIMIT batch_size
            )
            RETURNING id
        )
        SELECT COUNT(*) INTO processed FROM deleted;
        
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;