Skip to Main Content
Forums

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

Timo Hahn

You get a 'Read time out' error.
What is the service doing?
Have you tried to call the service synchronously?

Timo

kumar santosh

I tried both way its giving same error. I am trying to execute multiple vos(view criteria based ) and at last commiting one Eo based vo(nearly 1100 records) attribute by invoking this service.

Timo Hahn
Answer

Have you tried an easy WS something like a WS that just returns a string?
Timo

Marked as Answer by kumar santosh · Apr 9 2021
kumar santosh

Hi Timo,.

No I have not used any WS , Simply i have called configuration.createRootApplicationModule to get the amipl and then fetched some read only VOs and then done some commit . And exposed this method in service interface and tried invoking this after deploying this ear.

Earlier i was getting timeout exception after 30 seconds so I increased the JTA timeout from weblogic console to 5mins but now this readonlysocket timeout exception is coming. I am not sure if we need to set any parameters for this.

Timo Hahn

Why do you call configuration.createApplicationModule?
When you create a method in the application module and expüost ist as web service you don't need to handle the application module pooling yourself.
Try creating a simple methos in the AM that return a simple string like 'hello' and expose is as soap web service. Then try to call this method as a test. Once you get this working, you add more logic to the method (or create a new one that has more logic).

Timo

kumar santosh

Dear Timo,
I had already tested with for few employess(5) and it was successful and when I am considering for 1200 employees reading data its giving error.

I had created configuration.createApplicationModule because am and view objects was not accessible when I was trying testing with main method in AMPL that is the reason I used configuration.createApplicationModule? in appmodule to access the amimpl and vos.

kumar santosh

I have removed configuration.createApplicationModule and tried in normal way u said and tried again invoking still getting same issues. Its getting SocketTimeoutException exactly after 2 mins.

Timo Hahn

So, you know thth the WS works in principle. Good. The problem seems to be the time needed to get all rows you want to process.
Question: why would you need to transfer 1200 rows at once?
Think about some kind of pagination.
Or try to find out why the query or retriving hte data takes so much time. Sample: if you are only interested in reading data, use a VO in forward only mode. This speeds up reading a lot as no data has to be stored in hte internal cache system.

Timo

kumar santosh

Dear Timo,

Below are the answers for your query :

Question: why would you need to transfer 1200 rows at once?
-I have to update the records for this employees based on some logics calcualtion .

I tried to use setforward in Vo but no luck.

Timo Hahn

Question: why would you need to transfer 1200 rows at once?
-I have to update the records for this employees based on some logics calcualtion .But
But to update these records, you should not need to transfer them via a WS. Pass anything you need to update them to a method that does all the work in the model layer. This method you expose as WS and call it, passing hte right parameters.
Timo

1 - 10

Post Details

Added on Jan 30 2021
#12c, #delete, #performance, #plsql, #sql
25 comments
28,547 views
3 mentions