Re: Deleting all rows from a Table

From: Rob Zoeteweij <Rob.Zoeteweij_at_inter.nl.net>
Date: Fri, 17 Mar 1995 07:48:10 GMT
Message-ID: <D5Kr0A.HB4_at_inter.NL.net>


Greg Watt <tipps_at_ottawa.cbc.ca> wrote:

>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.

The fact that 24 integrity constraints are implemented might slow down the job. Here situations might occur where when deleting one row from your table, the RDBMS is cheching on several contraints if the PK to be deleted is present in another table.
Are you sure that this isn't a database design issue ?

>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
Received on Fri Mar 17 1995 - 08:48:10 CET

Original text of this message