Re: Deleting all rows from a Table

 In article <3kag1m$bap_at_noc.tor.hookup.net>, Greg Watt <tipps_at_ottawa.cbc.ca> writes:
> We have a table with no fewer than 24 integrity constraints. The table currently
> contains 9500 rows. How long should a DELETE table_name WHERE col_name = 'xxxxx'
> where col_name is an index? We find it's taking a lot of time. We are running on
> a HP 9000 800/G30. I'm not sure how much table space has been defined but I think
> out temp storage is 100 meg. The BOX itself has 1 2 gig drive. We tried doing the delete
> in PRO*C but it failed: we think it might have been the maxopencursors and the fact
> that it was at 10. We've upped it to 50 and resubmitted the job. The job's been
> running 7 hours before we killed it.
>
> Now we're trying the delete in SQL*PLUS. I take it ORACLE is opening all sorts of cursors
> to support the delete and this is things are taking so long. I'm sorry i can't add any more
> information that this - i'm new to ORACLE development. I guess while i have your attention,
> I should ask if there is a quicker way to delete records from a table. We've been a DATACOM
> shop for many years and our DBA's are just coming up to speed on ORACLE.
>
> Any comments would be appreciated.
>
> Greg Watt
>

One thing that will greatly improve the speed of your delete is to drop any index defined on your table. You can recreate the indexes after you've done with the delete. Another thing that you might consider to do is to temporarilly disable some or all of your constraints (but you need to make sure that your delete will not violate any of these constraints!).

-- 
------------------------------------------------------------------------------
Pierre Hollard                                 
Oracle Database Administrator                  Phone: (404) 827-0656
Turner Broadcasting System, Inc.              E-Mail: phollard_at_dev1.turner.com
------------------------------------------------------------------------------