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!

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

jflack

I think you should include information about what you are trying to accomplish - what is your use case. A shell script might not be the best solution, and certainly raises security concerns.

Vallabha

Hi Jflack
Thanks for your comments
Below is the Use Case
We are in the process creating a UI in APEX which need to call serial of Shell Scripts ( it is all related Apps DBA tasks ) and need to send the output to user.

InoL

Still not clear what you are trying to do.
Shell script running on the client? That is not possible from a browser.
Shell script running on the database server? Use dbms_scheduler for that.
Somewhere else? What is triggering the script?

Mike Kutz

Example DBA Tasks?
The ORDS team has REST-enabled a few of them (ORDS v19.1)
Other tasks (common for other RDBMS) may not be needed.
MK
REST Data Services Release Notes 19.1 (0 Bytes)

Billy Verreynne

This is not a Use Case.
This is your solution to an unknown problem.
Define the problem.

Vallabha

We are in the process of automating the EBS Apps cloning process for it we want to call Shell Scripts from Apex so it we wanted to know how we can call it.

InoL

I assume you are talking about scripts on the database server then. An Apex web application cannot call shell scripts directly from the browser, but the database can. Use dbms_scheduler.

Billy Verreynne

Shell scripts where? Running as which o/s users? Using what o/s environments?
APEX is a PL/SQL framework. It does not run "scripts", with the exception of Javascript that is send to the APEX client (web browser/web client) for execution.
PL/SQL can run external processes via the EXT PROC interface, via the Java VM accessing the o/s, or via DBMS Scheduler.
PL/SQL also has a web interface (expanded upon with APEX) for executing web CGI processes, and web services.
DBMS Scheduler is the easiest secure option. However it runs local processes on the database server in a non-privileged account - in other words a non-oracle and non-grid o/s account. Which ceases to be secure when you makes that a privileged account like that of an app server, or oracle itself.
This means using features like Sudo - and using these very carefully and not violating basic security principles. Which very likely means that very few to none of your existing scripts would be safe to run as is.
If the process execution also crosses server boundaries then approaches like ssh with authorised keys need to be considered.
All these issues makes the question "how to execute scripts from APEX?" a dangerously ignorant and naive question.

Yevon

This has nothig to do with APEX, this is just oracle DB PLSQL development. You can call database shceduler that calls to shell scripts, Just try this:
Just search in google: "oracle shceduler execute shell scripts" and you have nice article in dba-oracle

Billy Verreynne

dba-oracle is not a web site I would recommend for accurate technical detail.

jflack

Now were getting somewhere. I wonder if EBS Apps has an API that you can call instead of shell scripts. But if you must call shell scripts, I think a few people have given you good suggestions to use DBMS_SCHEDULER or an external procedure. Personally, I prefer scheduler.

Mike Kutz

I had an adjustment so that Google would no longer consider that site, but it stopped working. I haven't gone back to "fix" it.

1 - 12

Post Details

Added on Jan 30 2021
25 comments
29,163 views