Oracle 11.2中拆分ID表并批量删除8亿孤儿记录的存储过程咨询

Alright, let's tackle this Oracle 11.2 scenario step by step—14 billion records in table A, 8 billion of which are orphans tracked via their primary keys in table B. We need to split B into 8 manageable sub-tables (1 billion IDs each) and build an efficient batch delete procedure to remove those orphans without crashing your database or killing performance.

First, we need to break down the 8 billion IDs in table B into 8 smaller sub-tables. Two solid options here, depending on whether you care about ordered splitting or just even distribution:

Option 1: Ordered Splitting with ROW_NUMBER() #

If you need the IDs split in the order they exist in table B, use ROW_NUMBER() to assign sequential numbers, then carve out ranges for each sub-table. This uses CTAS (Create Table As Select) which is fast, especially with NOLOGGING to minimize redo generation.

-- Create first sub-table (1 billion IDs)
CREATE TABLE B_1 NOLOGGING AS
SELECT id
FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM B
)
WHERE rn BETWEEN 1 AND 100000000;

-- Repeat for B_2 to B_8, adjusting the range each time:
-- B_2: rn BETWEEN 100000001 AND 200000000
-- B_3: rn BETWEEN 200000001 AND 300000000
-- ...
-- B_8: rn BETWEEN 700000001 AND 800000000

Note: NOLOGGING skips redo logging (critical for speed here). Just make sure you take a backup of these sub-tables if you need to retain them long-term.

Option 2: Even Distribution with ORA_HASH() #

If order doesn't matter and you just want evenly sized sub-tables, use ORA_HASH() to hash the ID values and split them across 8 buckets. This avoids sorting the entire 8 billion rows, saving time and memory:

-- Create sub-table B_1 (1/8 of the IDs)
CREATE TABLE B_1 NOLOGGING AS
SELECT id FROM B WHERE MOD(ORA_HASH(id), 8) = 0;

-- Create B_2 to B_8 with MOD values 1 through 7:
CREATE TABLE B_2 NOLOGGING AS
SELECT id FROM B WHERE MOD(ORA_HASH(id), 8) = 1;

CREATE TABLE B_3 NOLOGGING AS
SELECT id FROM B WHERE MOD(ORA_HASH(id), 8) = 2;

-- ... continue for B_4 to B_8

Pro Tip: ORA_HASH distributes values more evenly than a simple MOD(id,8) especially if your primary keys aren't sequential.

Deleting 8 billion records in one go is a disaster waiting to happen (lock contention, undo exhaustion, slow performance). We'll build a procedure that deletes in small batches, processes each sub-table one at a time, and logs progress.

First, create a logging table to track batches (optional but highly recommended for monitoring):

CREATE TABLE DELETE_ORPHAN_LOG (
    sub_table_name VARCHAR2(30) NOT NULL,
    batch_number NUMBER NOT NULL,
    records_deleted NUMBER NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL
);

Now the stored procedure:

CREATE OR REPLACE PROCEDURE PURGE_ORPHAN_RECORDS
IS
    v_batch_size NUMBER := 100000; -- Adjust based on your system's undo capacity
    v_records_deleted NUMBER;
    v_current_batch NUMBER;
    -- Array to hold our 8 sub-table names
    TYPE sub_table_list IS TABLE OF VARCHAR2(30);
    v_tables sub_table_list := sub_table_list('B_1','B_2','B_3','B_4','B_5','B_6','B_7','B_8');
BEGIN
    -- Loop through each sub-table
    FOR tbl_idx IN 1..v_tables.COUNT LOOP
        v_current_batch := 1;
        DBMS_OUTPUT.PUT_LINE('Starting deletion for sub-table: ' || v_tables(tbl_idx));
        
        -- Keep deleting batches until no more records are found
        LOOP
            -- Delete a batch of records from table A using the current sub-table's IDs
            DELETE /*+ PARALLEL(A, 4) */ -- Use parallelism (adjust 4 to match your CPU cores)
            FROM A
            WHERE id IN (
                SELECT id
                FROM v_tables(tbl_idx)
                WHERE ROWNUM <= v_batch_size
            );
            
            v_records_deleted := SQL%ROWCOUNT;
            
            -- Log the batch results
            INSERT INTO DELETE_ORPHAN_LOG (
                sub_table_name, batch_number, records_deleted, start_time, end_time
            ) VALUES (
                v_tables(tbl_idx), v_current_batch, v_records_deleted, SYSTIMESTAMP, SYSTIMESTAMP
            );
            
            COMMIT; -- Commit after each batch to free up undo space
            
            EXIT WHEN v_records_deleted = 0; -- Exit loop when no more records to delete
            v_current_batch := v_current_batch + 1;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('Completed deletion for ' || v_tables(tbl_idx));
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('All orphan records have been successfully deleted!');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLERRM);
        RAISE; -- Re-throw the error to ensure it's visible
END;
/

• Indexing: Ensure table A's primary key id has an index (it should, since it's a PK). Add an index on each sub-table's id column to speed up the join/IN query.
• Parallelism: Adjust the PARALLEL(A, n) hint where n is the number of CPU cores you can dedicate to this task. Oracle 11.2 supports parallel DML, which will drastically speed up deletes.
• Undo Tablespace: Make sure your undo tablespace is sized appropriately. If you get "snapshot too old" errors, reduce v_batch_size.
• Off-Peak Execution: Run this procedure during non-business hours to avoid impacting live traffic.
• Validation: After deletion, verify the count:

  -- Check remaining records in A (should be 6 billion)
  SELECT COUNT(*) FROM A;
  -- Verify total deleted matches table B's count
  SELECT SUM(records_deleted) FROM DELETE_ORPHAN_LOG;
  

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