Oracle同表分区对比需求：基于Col2去重值跨分区匹配并生成新表

Alright, let's work through this problem. I've put together a PL/SQL solution that handles comparing a specified partition's distinct Col2 values against the other four partitions in your table, and logs the matches in a new table. Here's the breakdown:

• First, we'll create a dedicated results table to store cross-partition Col2 matches (if it doesn't already exist). This table will track which partitions were compared, the matching Col2 value, and timestamps for audit.
• We'll accept the target partition name as an input parameter so you can run this for any partition on demand.
• Extract all distinct Col2 values from the target partition in one go for efficiency.
• Loop through the remaining four partitions, pull their distinct Col2 values, and find overlaps with the target's values.
• Insert any matching values into the results table, ensuring no duplicate entries via a primary key.

CREATE OR REPLACE PROCEDURE compare_partition_col2(
    p_target_partition_name IN VARCHAR2
) IS
    -- Collection type to hold distinct Col2 values from the target partition
    TYPE col2_value_list IS TABLE OF your_partitioned_table.col2%TYPE;
    v_target_col2s col2_value_list;
    
    -- Cursor to fetch all partitions except the target one
    CURSOR c_other_partitions IS
        SELECT partition_name
        FROM user_tab_partitions
        WHERE table_name = 'YOUR_PARTITIONED_TABLE'  -- Replace with your actual table name
          AND partition_name != p_target_partition_name;
          
    v_current_partition VARCHAR2(30);
BEGIN
    -- Step 1: Create results table if it doesn't exist
    BEGIN
        EXECUTE IMMEDIATE '
            CREATE TABLE partition_col2_matches (
                target_partition VARCHAR2(30) NOT NULL,
                compare_partition VARCHAR2(30) NOT NULL,
                matched_col2 your_partitioned_table.col2%TYPE NOT NULL,
                created_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
                PRIMARY KEY (target_partition, compare_partition, matched_col2)
            )';
    EXCEPTION
        WHEN OTHERS THEN
            -- Ignore "table already exists" error
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
    END;
    
    -- Step 2: Grab distinct Col2 values from the target partition
    SELECT DISTINCT col2
    BULK COLLECT INTO v_target_col2s
    FROM your_partitioned_table PARTITION (p_target_partition_name);
    
    -- Step 3: Loop through other partitions and find matches
    OPEN c_other_partitions;
    LOOP
        FETCH c_other_partitions INTO v_current_partition;
        EXIT WHEN c_other_partitions%NOTFOUND;
        
        -- Insert matches into results table
        INSERT INTO partition_col2_matches (target_partition, compare_partition, matched_col2)
        SELECT p_target_partition_name, v_current_partition, t.col2
        FROM (SELECT DISTINCT col2 FROM your_partitioned_table PARTITION (v_current_partition)) t
        WHERE t.col2 MEMBER OF v_target_col2s;
        
        -- Optional: Commit after each partition if dealing with large datasets
        COMMIT;
    END LOOP;
    CLOSE c_other_partitions;
    
    DBMS_OUTPUT.PUT_LINE('Comparison complete! Check partition_col2_matches for results.');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Target partition ' || p_target_partition_name || ' does not exist or has no data.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        RAISE;
END;
/

1. Replace Placeholders: Make sure to swap YOUR_PARTITIONED_TABLE with the actual name of your partitioned table.
2. Results Table: The partition_col2_matches table is designed to avoid duplicate entries (thanks to the primary key) and includes timestamps for tracking when matches were logged.
3. Efficiency: Using BULK COLLECT to fetch target Col2 values reduces context switches between PL/SQL and SQL, which is much faster than row-by-row processing.
4. Flexibility: The cursor pulls all non-target partitions dynamically from user_tab_partitions, so you don't have to hardcode partition names if they change later.

To run the procedure, just execute this with your target partition name:

EXEC compare_partition_col2('YOUR_TARGET_PARTITION_NAME');

Then query the results table to see all matches:

SELECT * FROM partition_col2_matches ORDER BY target_partition, compare_partition;

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