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

dvohra21

It is documented.

"The Oracle.connect() method defaults to a false setting of the auto-commit flag. However, it also has signatures to set it explicitly. In the Oracle JDBC implementation, the auto-commit flag defaults to true."

Key Programming Considerations

kdario

Well, this is documentation for sqlj and first sentence just confirms what I said(that auto-commit by default should be false) 

Just to clarify my original post:

When you retrieve jdbc connection in adf application(for example, in app module impl class), that connection already has autocommit=false (because framework will set this property)

Difference between adf 11.x and 12.x is in setting db proxy user (by using conn.openProxySession() ) :

In 11.x (which uses ojdbc6 driver) invocation of openProxySession() method will preserve autocommit=false

In 12.x (which uses ojdbc7 driver) invocation of openProxySession() method will reset autocommit to true (and adf app will not work correctly with this setting)

Dario

dvohra21

ojdbc7.jar is based on JDBC 4.1 and JDBC 4.1 still sets auto-commit to true by default.

"The default is for auto-commit mode to be enabled when the Connection object is

created."

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf?AuthParam=1404073866_5e28356fa3b11ea367d6b…

Could auto commit be false for the PROXYTYPE_USER_NAME specified?

OracleConnection (Oracle Database JDBC Java API Reference)

1 - 4

Post Details

Added on Jan 30 2021
25 comments
28,545 views