HANA事务日志溢出风险咨询：千万级数据INSERT SELECT操作

Will INSERT INTO A SELECT * FROM B cause excessive transaction log growth in HANA for millions of rows? #

Short answer: Yes, by default it absolutely will—and this can lead to log full errors, performance bottlenecks, or even downtime if you’re not prepared. Here’s why, and what you can do about it:

Why the log bloat happens #

HANA treats a single INSERT ... SELECT statement as one atomic transaction. For every row inserted into table A, HANA has to write detailed redo/undo logs to ensure transaction consistency (so it can roll back if something fails, or recover after a crash). With tens of millions of rows, this adds up to an enormous amount of log data—easily filling up your log volumes if they’re not sized for this kind of workload.

Better alternatives to avoid log issues #

1. Split the operation into smaller, committed batches

Instead of shoving all rows into one transaction, break the insert into chunks (e.g., 100k rows at a time) and commit each batch. This keeps individual transaction logs small and manageable. You can do this with a stored procedure in HANA:

DO
BEGIN
    DECLARE v_offset INT := 0;
    DECLARE v_batch_size INT := 100000; -- Adjust based on your system's capacity
    DECLARE v_total_rows INT;

    SELECT COUNT(*) INTO v_total_rows FROM B;

    WHILE v_offset < v_total_rows DO
        INSERT INTO A 
        SELECT * FROM B 
        LIMIT v_batch_size OFFSET v_offset;
        
        COMMIT; -- Flush log after each batch
        v_offset := v_offset + v_batch_size;
    END WHILE;
END;

Note: If table B is being updated while you’re running this, you might end up with duplicate or missing rows. For static data (no writes to B during the copy), this works perfectly. For dynamic tables, consider locking B temporarily or using a snapshot.

2. Use CREATE TABLE ... AS SELECT (CTAS) for faster, lower-log copies

If you don’t need to preserve existing data in table A (or can recreate A from scratch), CTAS is a far more efficient option. HANA treats CTAS as a DDL operation, which bypasses most transaction logging—it directly creates the table and writes data to storage without per-row redo/undo entries.

-- Create table A with the same structure and data as B
CREATE TABLE A AS SELECT * FROM B;

Caveat: CTAS won’t copy indexes, primary keys, constraints, partitions, or triggers from B. If you need those, first replicate the table structure with:

CREATE TABLE A LIKE B INCLUDING ALL; -- Copies all schema objects

Then use the batch insert method above to populate it, or combine with unlogged mode (below) for faster loading.

3. Use unlogged mode (use with caution!)

If you’re loading into a new, empty table and can tolerate the risk of data loss if the operation fails, you can disable logging temporarily for the insert:

ALTER TABLE A SET LOGGING MODE UNLOGGED;
INSERT INTO A SELECT * FROM B;
COMMIT;
ALTER TABLE A SET LOGGING MODE LOGGED; -- Re-enable logging immediately

Warning: Unlogged operations don’t write transaction logs, so if the insert fails mid-way, you’ll have a partially filled table with no way to roll back. Also, unlogged tables aren’t included in regular backups until you re-enable logging. Only use this for one-time, low-risk loads.

Final tips #

• Always check your HANA log volume free space before starting large data operations.
• Monitor log growth in the HANA Studio or via SQL (SELECT * FROM M_LOG_FILES;) during the process.
• For extremely large datasets, consider using HANA’s Data Provisioning tools which are optimized for bulk data movement with minimal log impact.

内容的提问来源于stack exchange，提问作者Guoping Zhang