Re: "Delete From <table>" taking all day

In <4vnork$ko6_at_sulu.prodata.de> guther_at_privat.prodata.de (Jochen Guther) writes:

>meskillw_at_ncr.disa.mil (Bill Meskill) wrote:
 

>>Hi,
>>I have a 4000 row table that's taking quite a long
>>time to delete. What's going on?
 

>>Bill Meskill
 

>>FYI. I have my data and indexes separated on lots of
>>different disks and have allocated 80M for the
>>SGA (a full database export is 10M). There are only
>>2 people on the server.
 

>Have you tried "truncate table" ?

Although this obviously works, it doesn't answer the poster's original question. Also, you cannot truncate a table you do not own, unless you have the "DELETE ANY TABLE" privilege, which the DBA's around here don't seem to want the developers to have :-).

Anyway, to answer the question, "What's going on?", the command "DELETE FROM <tablename>;" is one transaction; therefore Oracle has to load up all that data into a rollback segment, so that if your next command is "ROLLBACK WORK", all the deleted rows can be automagically restored.

If you cannot "TRUNCATE" the table, write a short PL/SQL block with a cursor to read the table and delete each row. Then after each group of so many rows (100? 500?), do a COMMIT. Note: you may want to re-open the cursor after each commit, to avoid -1555 (Rollback segment too small) errors.

-- 
------------------------------------------------------------------------------
Computer General		Rochester, NY			(716) 436-6372
      "... providing general computer solutions to specific business problems"
                              gwn_at_servtech.com