• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2492
  • Last Modified:

the fastest way to delete

If table EMP has 30 millions of rows, what is the fastest way to delete?  Will index on DEPT_NO make it faster?  Is there a hint like DELETE /*unrecoverable*/ to make the delete faster?  Right now, the delete of 1 million rows takes a few minutes.  Thanks!

DELETE FROM EMP WHERE DEPT_NO =10;

0
ewang1205
Asked:
ewang1205
  • 4
  • 4
  • 4
  • +3
6 Solutions
 
schwertnerCommented:
The WHOLE table:

ALTER TABLE emp TRUNCATE;

should be the faster but UNRECOVERABLE way.

If you would like to delete only PART of the rows and the table has many indexes
it will be good to disable the indexes, to delete the rows and to recreate the indexes.

DML operations over tables with many indexes work slowly.
0
 
Jinesh KamdarCommented:
TRUNCATE TABLE emp;
0
 
ewang1205Author Commented:
Delete only  DEPT_NO =10.  TRUNCATE table is not an option here.  This table doesn't have index.  Thanks.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jinesh KamdarCommented:
Does it have any constraints that have been set to ON DELETE CASCADE ?
0
 
ewang1205Author Commented:
No constraints that have been set to ON DELETE CASCADE .
0
 
Jinesh KamdarCommented:
Hmm ... I guess then it already is as good as it could get.

@schwertner: Do u think NOLOGGING will help in this case?
0
 
Jinesh KamdarCommented:
Scratch that, silly of me to even ask that question when he can't use TRUNCATE!
0
 
sdstuberCommented:
"Will index on DEPT_NO make it faster? "

Depends.  Is DEPT_NO very selective for the department you're trying to delete?

If you have 8 records (for instance) for dept_no=10,  then yes, the index should help immensely.

If you have 25 million records for dept_no=10, then the index would probably be ignored, and would likely make things much worse if was used.

For data amounts inbetween those two extremes, it's not so much the quantity of rows as the distribution of them across data blocks.  Naturally, more rows means more blocks.

If your table is partitioned by dept_no, that can help immensely as well.  Simply drop the partition containing department 10.

Other than that, no, there's not really a lot you can do to make the delete go faster.  sorry

0
 
sujith80Commented:
Can you affort to have to have some extra space?
Try this:
create table EMP_TEMP
as select * from EMP WHERE 1 = 2
/
alter table EMP_TEMP nologging
/
insert /*+ APPEND */
into EMP_TEMP
select * FROM EMP WHERE DEPT_NO <> 10
/
alter table EMP_TEMP logging
/
rename EMP to EMP_OLD
/
rename EMP_TEMP to EMP
/

Open in new window

0
 
sdstuberCommented:
If "most" of the data will be deleted and only a small portion will be kept, then yes, sujith80's suggestion will, work for speed,  it can also cause mass invalidations and force you to recompile but that's a minor and easy to fix.

Look at this option only if you are keeping a small portion of your data and/or you want to reclaim the space of your deleted data with no plans to repopulate it.
0
 
sujith80Commented:
>> it can also cause mass invalidations
agree sdstuber, and indeed partitioning is the best way to go.
0
 
schwertnerCommented:
NOLOGGING works only to log DDLs.
It is not possible to avoid entries in undo and redo logs.
The only way to speed up is to use a procedure that will commit
after every e.g. 500 or 1000 rows and so will empty the undo logs.
0
 
rbrookerCommented:
you can delete using rowid's.

this code does a single tablescan looking for all dept_no = 10 records and stores the rowids.  the records are then deleted using the rowid to locate them ( very very fast ).  once 10000 records have been deleted, a commit is issued.

from a previous question, this was the fastest way - http://www.experts-exchange.com/Database/Oracle/Q_22123659.html


declare
  cursor c1 is select rowid from emp where dept_no = 10;
  cnt pls_integer := 0;
begin
  for r1 in c1 loop
    delete emp where rowid = r1.rowid;
    cnt := cnt + 1;
    if( mod( cnt, 10000 ) = 0 ) then
      commit;
    end if;
  end loop;
  commit;
end;

Open in new window

0
 
sdstuberCommented:
I wouldn't use the the procedural approach above for multiple reasons.

If you have many rows to delete, you're issuing a separate statement for each row.
Even using rowid as a lookup, that's way to much work.

Also, committing frequently often INCREASES execution time, because, again, it adds extra steps.

However, periodic commits can sometimes be necessary due to limits on undo.

And last,  mod is fairly expensive.  It's faster to simply reset your counter.
If you must do periodic commits, then make the reset as large as possible.
Do 100,000 at a time instead of 10,000.  Do a million at a time if you can.

if cnt > 100000 then
    commit;
    cnt := 0;
end if;



0
 
sdstuberCommented:
ooops, I critiqued the previous post, but forgot to include my alternate.
This should be MUCH faster than looping one row at a time.

change the 100000 to as big of a number as your undo will allow

LOOP
            DELETE FROM emp
                 WHERE dept_id = 10
               AND ROWNUM <= 100000;

            EXIT WHEN SQL%ROWCOUNT = 0;
            COMMIT;
END LOOP;
0
 
ewang1205Author Commented:
A few people mentioned about periodic commits.  I see that helps in insert.  I see sdstuber solution is the best one, although I have not tried yet.

LOOP
            DELETE FROM emp
                 WHERE dept_id = 10
               AND ROWNUM <= 100000;

            EXIT WHEN SQL%ROWCOUNT = 0;
            COMMIT;
END LOOP;
0
 
ewang1205Author Commented:
Based on my other experiences, more commit does speed up.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now