Improving the performance of keeping the three recent records of each account query

Question

Hi, 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?

Answer 1

You can do that with analytics (although I am not at all well versed in it myself). Take a look at this question, which seems to address a situation similar to yours:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1212501913138

Answer 2

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.