Oracle SQL存储过程DEL_OBS索引优化咨询

Let's break down the issues with your current approach and walk through practical, index-focused optimizations—starting with the quick answer to your ROWNUM index question:

First: ROWNUM Indexes Aren't Feasible #

ROWNUM is a pseudo-column generated dynamically during query execution, not stored in the table. Oracle can't create an index on a value that doesn't exist until the query runs, so that approach won't work. We need to pivot to better strategies.

Why Your Current Procedure Struggles #

Your existing code has two major performance bottlenecks:

1. Row-by-row deletion: The FOR LOOP executes a separate DELETE for every row, which causes massive context switching between PL/SQL and SQL—catastrophic for large cuantos values.
2. Expensive random sorting: ORDER BY DBMS_RANDOM.VALUE forces a full table scan and sorts every row in the table just to pick a random subset. On large tables, this is painfully slow.

Step 1: Optimize the Random Row Selection #

Instead of sorting the entire table, we can use a lighter-weight method to pick random rows while leveraging indexes. Assuming (nplate, odatetime) is your table's primary/unique key (it's used to identify rows for deletion), here's a better way to select random rows:

SELECT nplate, odatetime
FROM (
    SELECT nplate, odatetime,
           ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS rn
    FROM observations
)
WHERE rn <= cuantos;

This still uses random sorting, but by only selecting the key columns (instead of *), we minimize data processing overhead.

Step 2: Replace Row-by-Row Deletion with Bulk Deletion #

The biggest win comes from ditching the loop and deleting all target rows in a single statement. This eliminates context switching and reduces undo/redo overhead.

Optimized DEL_OBS Procedure #

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number) IS
BEGIN
    DELETE FROM observations
    WHERE (nplate, odatetime) IN (
        SELECT nplate, odatetime
        FROM (
            SELECT nplate, odatetime,
                   ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS rn
            FROM observations
        )
        WHERE rn <= cuantos
    );
    COMMIT; -- Adjust based on your transaction requirements
END DEL_OBS;
/

Step 3: Create the Right Index to Speed Up Everything #

To make both the random selection and bulk deletion fast, create an index on the key columns used to identify rows:

CREATE INDEX idx_obs_key ON observations(nplate, odatetime);

This index does two critical things:

• For the random selection query: Oracle can use an index fast full scan instead of scanning the entire table. This is far faster because index blocks are smaller and more compact.
• For the delete: Oracle uses the index to quickly locate the exact rows to delete, avoiding full table scans.

Bonus: Batch Deletion for Large cuantos Values #

If you're deleting a large portion of the table (e.g., >10% of rows), a single bulk delete can strain undo/redo logs. Split the deletion into smaller batches to mitigate this:

CREATE OR REPLACE PROCEDURE DEL_OBS(cuantos number) IS
    v_deleted_count NUMBER := 0;
    v_batch_size CONSTANT NUMBER := 1000; -- Adjust based on your system
BEGIN
    WHILE v_deleted_count < cuantos LOOP
        DELETE FROM observations
        WHERE (nplate, odatetime) IN (
            SELECT nplate, odatetime
            FROM (
                SELECT nplate, odatetime,
                       ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS rn
                FROM observations
            )
            WHERE rn <= LEAST(v_batch_size, cuantos - v_deleted_count)
        );
        
        v_deleted_count := v_deleted_count + SQL%ROWCOUNT;
        COMMIT; -- Commit each batch to free up undo space
    END LOOP;
END DEL_OBS;
/

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