performance - 提高保留每个帐户查询的三个最近记录的性能

Question

我在 Oracle (10g XE) 数据库中有一个表，我要清理它，只保留每个帐户的三个最近记录。这是我现在正在做的事情:

CREATE TABLE ACCOUNT_TRANSACTION_TMP NOLOGGING AS SELECT * FROM ACCOUNT_TRANSACTION WHERE 1=2;


DECLARE
    CURSOR mbsacc_cur (account_id_var account_transaction.account_id%TYPE) IS
        SELECT * FROM account_transaction WHERE account_id = account_id_var ORDER BY transaction_time DESC;

    account_transaction_rec account_transaction%ROWTYPE;
BEGIN
    FOR i IN (SELECT DISTINCT(account_id) FROM account_transaction) LOOP
        OPEN mbsacc_cur(i.account_id);
        LOOP
            FETCH mbsacc_cur INTO account_transaction_rec;
            EXIT WHEN mbsacc_cur%NOTFOUND OR mbsacc_cur%ROWCOUNT > 3;
            INSERT /*+ append */ INTO account_transaction_tmp VALUES account_transaction_rec;
        END LOOP;
        CLOSE mbsacc_cur;
    END LOOP;
END;
/

然后我将删除旧表，将这个新表重命名为旧表并添加约束。

但问题是上面的代码永远运行(约 3-4 小时)大约 100 万条记录，其中大约一半应该被删除。

有什么办法可以提高这个性能吗？

Answer 1

不是创建一个空表并以 RBAR 方式填充，而是创建一个包含您想要的行的表......

CREATE TABLE ACCOUNT_TRANSACTION_TMP NOLOGGING AS 
SELECT account_id, col1, col2, col3, transaction_time from
    ( select at.*
             , row_number()
                  over (partition by at.account_id 
                         order by at.transaction_time desc) as to_keep
 FROM ACCOUNT_TRANSACTION at)
where to_keep <= 3
/

然后直接跳到计划的重命名部分。