Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Fastest way to batch delete data from a table with 1 billion rows

OraCJan 30 2021 — edited Jan 30 2021

Hi,
I need some help deleting batches from a really large online transactions table (up to 1 billion records). I hope to delete around 9 million records daily. 9 million more are being added daily. I have an off-peak time window when customer usage is limited so I can try to run this optimally, but I'm also conscious of not impacting any potential customers too much by specify too high a batch size below(10,000). Its Oracle 12.2 Standard Edition so unfortunately partitioning is not an option. I've come up with the following but its just not deleting fast enough. The initial select seems to be ok, its more about my loop. Is there a more efficient way of batching this?
DECLARE
cursor cur is
select /*+ index_ffs(a,P_ITD) parallel_index(a,P_ITD,4) */ C_ID from ITD a WHERE CREATED < '27-NOV-20 12.00.00.000000 AM';
TYPE CII_TYPE IS TABLE OF NUMBER;
CII_TYPE_TBL CII_TYPE;
BEGIN
OPEN CUR;
LOOP
FETCH CUR BULK COLLECT INTO CII_TYPE_TBL LIMIT 10000;
FORALL i IN 1..CII_TYPE_TBL.COUNT
DELETE FROM ITD WHERE C_ID=CII_TYPE_TBL(i);
COMMIT;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
END;
/
P_ITD is the primary key constraint on the ITD table on C_ID
CREATED_ON is also indexed separately.
Thanks

Comments

NickR2600-Oracle

Hi Carol,

    I don't mind the questions, not at all.  One of the benefits of the game is that it spurs people have conversations about Java.  I'm glad you're poking around the code and being curious.  And if any lesson were to give people trouble, it would be the final lesson.

1) You could initialize the transactions field to 0 from the Account constructor.  However, it's not necessary.  If a number field (like an int or double) isn't explicitly given an initial value, its value becomes 0.  In other words, it defaults to 0.  Correct, transactions would be an instance variable.

2) NewFXMain is where the ArrayList is first created and where account instances are added to that list.  But while that's going on, the ButtonController class has no idea that particular ArrayList even exists!  All it knows is that it'll have to work with some sort of ArrayList.  A buttonController instance wouldn't be able to work with an ArrayList unless we explicitly tell it which ArrayList instance we're talking about.  This line of code from NewFXMain creates a ButtonController instance named buttonController and tells it which ArrayList to use:

ButtonController buttonController = new ButtonController(accountList, ownerSearchBar, numberSearchBar, btn1, btn2, btn3, btn4, btn5, btn6, btn7, btn8);

The ButtonController class saves this ArrayList as a field.  The variable used to save this information is coincidentally is also called accountList.  We could name the variable whatever we want, and it will still point to the same ArrayList created in NewFXMain.

Nick

3462211

Thanks a lot for your explanation Nick.  For question 1), I should have copy-typed your code (please see below) or worded my question a bit better.  I think I meant to ask:  Is there any particular reason you choose to initialize the instant variable transactions in the field declaration area instead of constructor?

public abstract class Account {

    //Fields

    protected String accountOwner;

    protected double balance;

    protected int accountNum;

    protected int transactions = 0;

    protected static int nextAccountNum = 0;

   

   

    //Constructor

    public Account(String o, double b){

        accountOwner = o;

        balance = b;

        setAccountNumber();

        System.out.println("New Account:");

        printDetails();

    }

Carol

NickR2600-Oracle

Hmm... I may have been thinking "For every field which doesn't require a value to be passed to the constructor, I'll set its initial value where the field is declared." 

Nick

1 - 3

Post Details

Added on Jan 30 2021
25 comments
28,645 views