提高保留每个账户查询最近3条记录的性能

发布于 2024-08-20 09:04:07 字数 982 浏览 5 评论 0原文

我在 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 万条记录，其中大约一半应该被删除。

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

原文

I've a table in an Oracle (10g XE) database, and I'm going to clean it up and only keep the three recent records of each account. Here is what I'm doing right now:

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;
/

And then I'll drop the old table, rename this new one to old one and add constraints.

But the problem is the above code runs forever (~3-4 hours) for about 1 million record which approximately half of them should be removed.

Is there any way to improve the performance of this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

回忆凄美了谁 2024-08-27 09:04:07

不要创建一个空表并以 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
/

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

Instead of creating an empty table and populating in an RBAR fashion create a table with the rows you want....

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
/

Then skip straight to the renaming part of your plan.

回复收藏 0 原文