Skip to Main Content
  • Questions
  • Delete data from a table containing millions of rows based on a condition

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, David.

Asked: June 03, 2022 - 9:45 am UTC

Last updated: June 06, 2022 - 12:56 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 1000+ times

You Asked

Hi Tom,

I have a requirement where I need to delete data from a table containing millions of rows based on a where clause(trx_date <=sysdate-number_of_days)</code>. This will be a scheduled job in Oracle EBS so it will basically be online.

Could you please suggest the fastest way to do this operation keeping in mind that it is an online operation and will be run everyday?

Regards
David

and Chris said...

When deleting a large fraction of the rows from a table, it's generally best to change the process to DDL. For example, create-table-as-select, filtered table move, etc. I cover these in detail at:

https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql

This tends not to work with daily deletion though. Although you're removing millions of rows each time, it's generally a small fraction of the table.

In this case, the best option is to partition the table. Then remove the oldest rows by truncating or dropping the oldest partition.

I covered this in detail in last month's SQL Office Hours:



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.