Oracle - Large Bulk Insert or Delete

oracle

There are various approaches for doing large bulk insert or delete efficiently. Each approach has its advantages and disadvantages, so choose your tool carefully. We must evaluate the advantages and disadvantages of each approach for our particular situation (availability, size of our tables, etc). It is recommended that, if possible, we should maintain an environment that is a copy of production database on identical hardware, but completely separate from production environment, and try out each of these approach on this environment and measure the performance of each approach before we decide on the best candidate. I typically configure this environment as a slave replica of the production database. When I need to do benchmarking like this, I:

1. stop replication on this slave database
2. stop the database on this slave
3. make backup copy of the hard drives
4. start up the slave database (but do not resume replication)
5. do the benchmarking
6. stop the slave database
7. delete the content on the drives
8. restore the content of the drives from backup
9. start the slave database
10. resume replication

Common approaches for doing large bulk inserts and deletes involves disabling indexes (or delete the indexes before doing bulk inserts / deletes, and re-create these indexes after the bulk inserts / deletes is done).

If we need to delete a large percentage of the table, we may consider creating a new table containing rows that should be kept, truncate the original table, and then re-insert the rows in the new table back into the original table.

For large bulk insert, we may want to:

1. create a new table (with same column definitions)
2. write a trigger so that when a new record is insert into the original table, this row is also insert into the new table
3. put a write lock on the original table
4. select all rows from the original table and insert them into the new table
5. remove the write lock on the original table
6. perform the bulk insert
7. create indexes on the new table
8. drop the old table
9. rename the new table to the old table name

This approach carry a possible data loss during the time that we drop the old table and when the rename operation is done.

The best approach: If we have a master-master setup, or a master-slave setup, we can stop replication, perform the bulk insert / delete on the slave, rebuild the indexes, bring it back online, wait for it to catch up with the master, and then promote the slave into the master.

Alternatively, we can write a stored procedure that delete 10000 rows at a time using a single delete statement, followed by a commit, and do this in a loop. If an exception occurs, we can re-run the procedure until it succeed. Example of such procedure:

-- This stored procedure demonstrate:
-- 1: using ROWNUM with DELETE
-- 2: using IF
-- 3: using WHILE (TRUE) LOOP and EXIT to break out of the loop when necessary
-- 4: using DBMS_OUTPUT.PUT_LINE to print debugging messages

CREATE OR REPLACE PROCEDURE QXPLORE.SP_PURGE_CMADT_SEARCH(entitiesID IN QXPLORE.ENTITIES.ENTITIESID%type, deleteLimit IN QXPLORE.ENTITIES.ENTITIESID%type)
IS
    CNT NUMBER;
    REMAINING NUMBER;
BEGIN
    IF (entitiesID = 0)
    THEN
        DBMS_OUTPUT.PUT_LINE('QXPLORE.SP_PURGE_CMADT_SEARCH  is being invoked with (' || entitiesID || ',' || deleteLimit || ')');
        SELECT COUNT(*) INTO CNT FROM QXPLORE.CMADT_SEARCH WHERE (ADMIT_DATE IS NOT NULL) AND (ADMIT_DATE < TRUNC(SYSDATE) - INTERVAL '90' DAY);
        DBMS_OUTPUT.PUT_LINE('We need to purge ' || CNT || ' records!');
        REMAINING := CNT;
        WHILE (TRUE) LOOP
            IF (REMAINING > 0)
            THEN
                DELETE FROM QXPLORE.CMADT_SEARCH WHERE (ADMIT_DATE IS NOT NULL) AND (ADMIT_DATE < TRUNC(SYSDATE) - INTERVAL '90' DAY) AND ROWNUM <= deleteLimit;
                REMAINING := REMAINING - deleteLimit;
                COMMIT;
                DBMS_OUTPUT.PUT_LINE('Remaining: ' || REMAINING);
            ELSE
                EXIT;
            END IF;
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('QXPLORE.SP_PURGE_CMADT_SEARCH  is being invoked with (' || entitiesID || ',' || deleteLimit || ')');
        SELECT COUNT(*) INTO CNT FROM QXPLORE.CMADT_SEARCH WHERE (HCOID=entitiesID) AND (ADMIT_DATE IS NOT NULL) AND (ADMIT_DATE < TRUNC(SYSDATE) - INTERVAL '90' DAY);
        DBMS_OUTPUT.PUT_LINE('We need to purge ' || CNT || ' records for HCOID ' || entitiesID || '!');
        REMAINING := CNT;
        WHILE (TRUE) LOOP
            IF (REMAINING > 0)
            THEN
                DELETE FROM QXPLORE.CMADT_SEARCH WHERE (HCOID=entitiesID) AND (ADMIT_DATE IS NOT NULL) AND (ADMIT_DATE < TRUNC(SYSDATE) - INTERVAL '90' DAY) AND ROWNUM <= deleteLimit;
                REMAINING := REMAINING - deleteLimit;
                COMMIT;
                DBMS_OUTPUT.PUT_LINE('Remaining: ' || REMAINING);
            ELSE
                EXIT;
            END IF;
        END LOOP;
    END IF;
END;
/

http://www.dba-oracle.com/t_oracle_fastest_delete_from_large_table.htm
http://www.dba-oracle.com/t_deleting_large_number_of_rows_in_oracle_quickly.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm
http://www.dba-oracle.com/t_disable_index.htm
http://www.dba-oracle.com/t_skip_unusable_indexes.htm