Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Poor perfmance after large delete

Re: Poor perfmance after large delete

From: Glenn Ferguson <glenn_at_home.net>
Date: 1997/04/24
Message-ID: <33603CB6.242D@home.net>#1/1

John C. wrote:
>
> Andreas Baumkötter wrote:
> >
> > mwagoner_at_iac.net (Mark Wagoner) wrote:
> >
> > >We had been testing our database setup with sample data and decided to try
> > >and go live. The main table had 1.4 million rows, which I deleted by doing
> > >a series of DELETE FROM WHERE statements (I tried to truncate the table,
> > >but Oracle said there were constraints even after I disabled them all, but
> > >that is another problem). After about 2 hours the main tables were empty
> > >so I went in and did a SELECT COUNT(*) to make sure. It took almost 3
> > >minutes for the result to come back! It took less than 2 seconds when the
> > >table was full!
> >
> > [some stuff deleted]
> >
> > Hello Mark,
> >
> > we have the same problem. Oracle told us that the internal hashing
> > tables are cleared but the memory is still in use. That makes the
> > things slow down.
> >
> > The only solution we've found ist to drop the table and then recreate
> > it. But to do this you have to drop and recreate all dependent
> > constraints and all indices too.
> >
> > So, as you might believe, we're not very happy with this solution. If
> > you find a better one, let us know please.
> >
> > Ciao, Andreas
> > -
> > Andreas Baumkötter, PRO DV Software GmbH, Dortmund, Germany
> > Email to: baumkoetter_at_prodv.de
>
> I think dropping table is the way to go. I do this all the time.
> whay is missing from this discussing is to drop the table with cascade option
> then the table will be dropped disregarding it has any dependend tables or
> not.
>
> Hope this helps.
> --
> John Chan
> Manager, Data Services
> RTE Asset Management (aka. Rightime Econometrics, Inc.)
> 1095 Rydal Rd.
> Rydal, PA 19046-1711
> 215-572-7288 (Voice)
> 215-572-6254 (Fax)
> Internet address: john_at_rte-asset.com

I have found that a trucate works as well. Oracle keeps some type of high water mark and truncate seems to clear it. You shouldn't get any constraint errors since you have no rows left at this point.

Glenn Ferguson
@Home Network
Oracle DBA
glenn_at_home.net Received on Thu Apr 24 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US