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;

ewang1205Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
SujithData ArchitectCommented:
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
SujithData ArchitectCommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.