Re: Deleting all rows from a Table

In article <3kag1m$bap_at_noc.tor.hookup.net>, Greg Watt <tipps_at_ottawa.cbc.ca> writes:
::From: Greg Watt <tipps_at_ottawa.cbc.ca>
::Newsgroups: comp.databases.oracle
::Subject: Deleting all rows from a Table
::
::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
::
::

 Greg,

 It sounds to me as though one or more of the other tables being checked by  the integrity contraint may not have indexes on them. Are these other tables  large ? If no indexes exist on the key it is checking a full table scan  will be performed for each row you are trying to delete. I have had similar  problems although this was a design error and not implemented through  Constraints (they were not implemented in V6).

 Hope this helps, it not feel free to E-mail me.

 Ian

-- 
+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+