best way to remove millions of rows

First of all, follow Jonathan suggestion and provide more information. Remembre to follow this guidelines:

Regarding your question and assuming:

1) That you have a similar amount of data every year

2) That you have no dependencies to that table (no foregin keys)

If you have 12.5 years of data and you want to delete 11 of them (keep 1.5) it may be worth considering just copying that data to a new table, drop the old one and rename the new one as the old, and reconstruct additional things like constraints, indexes, etc. Inserting 7.5 million rows (more or less 1.5 year) is something that an Oracle database can handle.

If you DO have references, then the work is more complicated (you need to reconstruct them after renaming) , but then your process of just deleting as you mentioned is also incomplete, you need to handle those data dependencies.

user8983130 wrote:I am glad to hear from you. The db version is 12.2.0.1 and it's enterprise edition with partitioning option. However, the app. vendor didn't give recommendation to partition of the table,instaed, suggested us to delete rows and then do the table move and index rebuild things. Additinally, the table is storing data that is not suitable for partitioning accoring to vendor.

Given that I would say your best bet is likely to

0) lock the table (ensure that other sessions aren't allowed to perform DML while you are doing this maintenance, so do whatever you need to do to accomplish that)

1) create a table with the rows you need to keep from the existing table

2) truncate existing table

3) insert (append) from table in step #1 into existing table

4) if everything is good drop the table created in step #1

I'd recommend that over dropping the existing table to avoid having grant issues; and since this is a vendored application you want to do as little as possible that may cause issues with it. Maybe schedule this process to happen annually?

Cheers,

12.2, Enterprise, with partitioning is the 2nd best place to be.

You can conver a heap table into a partitioned table, excluding data you don't want to keep, maintaining index, all online in one command.

In your case you could create a hash partitioned table with one partition, and it should behave the same way as far as the optimizer is concerned as a non-partitioned table,

See https://jonathanlewis.wordpress.com/2017/06/09/12-2-partitions/  for an example that converts a simple heap table into a list partitioned table - it covers all the high points.

Note - the "including rows where" clause - you don't wast any resources copying (with redo and undo) any rows you don't want.

L. Fernigrini's comment was a good one, by the way - can you make a good estimate of how much data you will have left - it's 1.5 years out of 12.5, but if the business has been growing steadily then it's not necessarily the case that you're keeping only 3/25ths (1.5/12.5) of the data, so it would be good to know how much of the data will disappear and how much remain.

Regards

Jonathan Lewis

UPDATE:  2nd best because I thought it wasn't until 18c or 19c that you could do "alter table t1 move including rows where coly >= {constant} online" - but it does actually work in 12.2 (though I'm still trying to find the place in the documentation that says it's supposed to work).

UPDATE 2: It is in the manual. Here's a reference to the 12.2 SQL Reference manual "Alter table" - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9… ,

follow down to "move_table_clause" https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9…

then down to "filter_condition"  https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9…