Skip to Main Content
  • Questions
  • which delete statement is better for deleting millions of rows

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 07, 2019 - 6:54 am UTC

Last updated: June 18, 2021 - 9:12 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

I have table which contains millions of rows.
I want to delete all the data which is over a week old based on the value of column last_updated.

so here are my two queries,

Approach 1:

Delete from A where to_date(last_updated,''yyyy-mm-dd'')< sysdate-7;




Approach 2:

l_lastupdated varchar2(255) := to_char(sysdate-nvl(p_days,7),'YYYY-MM-DD');
insert into B(ID) select ID from A where LASTUPDATED < l_lastupdated;
delete from A where id in (select id from B);




which one is better considering performance, safety and locking?

and we said...

Assuming the delete removes a significant fraction of the data & millions of rows, approach three:

create table tmp
  Delete from A where to_date(last_updated,''yyyy-mm-dd'')< sysdate-7;

drop table a;

rename tmp to a;


https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2345591157689

Obviously you'll need to copy over all the indexes, grants, etc. But online redefinition can help with this https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

When you get to 12.2, there's another simpler option: a filtered move.

This is an alter table move operation, with an extra clause stating which rows you want to keep:

create table t (
  c1 int
);

insert into t values ( 1 );
insert into t values ( 2 );
commit;

alter table t
  move including rows where c1 > 1;
  
select * from t;

C1   
    2 


While you're waiting to upgrade to 12.2+ and if you don't want to use the create-as-select method for some reason then approach 1 is superior:

- Both methods delete the same rows from A* => it's the same amount of work to do the delete
- Option 1 has one statement; Option 2 has two statements; 2 > 1 => option 2 is more work

*Statement level consistency means you might get different results running the processes. Say another session tries to update an old row that your process will remove.

With just the delete, the update will be blocked until the delete finishes. At which point the row's gone, so the update does nothing.

Whereas if you do the insert first, the other session can update & commit the row before the insert completes. So the update "succeeds". But the delete will then remove it! Which can lead to some unhappy customers...

Rating

  (2 ratings)

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

Comments

is ALTER...MOVE statement will invalid the referencing objects ?

SHREEKANT DEORE, June 17, 2021 - 2:57 am UTC

is ALTER...MOVE statement will invalid the referencing objects ?
Chris Saxon
June 18, 2021 - 9:12 am UTC

It's easy to check this:

create table t (
  c1 int
);
create or replace procedure p ( p int ) as
  trec t%rowtype;
begin
  select * into trec from t;
end p;
/

alter table t move;

select status from user_objects
where  object_name = 'P';

STATUS   
VALID 


So that would be NO; the objects remain valid (at least from 11.2 where I ran this test).

is ALTER...MOVE statement will invalid the referencing objects ?

SHREEKANT DEORE, July 06, 2021 - 7:32 am UTC

Thank you.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.