Xref: alice comp.databases.oracle.server:69501 Path: alice!news-feed.fnsi.net!newsfeed.direct.ca!news.maxwell.syr.edu!newsfeed2.news.nl.uu.net!sun4nl!bullseye.news.demon.net!demon!news.demon.nl!demon!sybrandb.demon.nl!not-for-mail From: "Sybrand Bakker" Newsgroups: comp.databases.oracle.server Subject: Re: How to tune this stored proc? Date: Tue, 12 Oct 1999 21:52:55 +0200 Message-ID: <939757986.27355.0.pluto.d4ee154e@news.demon.nl> References: <3803846C.AE7D0296@pacific.net.hk> Reply-To: "Sybrand Bakker" X-Trace: news.demon.nl 939757986 pluto:27355 NO-IDENT sybrandb.demon.nl:212.238.21.78 X-Complaints-To: abuse@demon.net X-Newsreader: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 Lines: 34 Is trandate indexed? You are purging about 10 percent of the table. This would urge for forcing a full table scan to be used in the delete. Hth -- Sybrand Bakker, Oracle DBA Lee Ming Fai wrote in message news:3803846C.AE7D0296@pacific.net.hk... > We have a table which contain about 4.5M records and need to purge about > 580000 records each day by transaction date. > The problem is that we found it takes too long, more than 4 hours to > delete these records. The following is the stored proc we use, is there > any better method or how to tune this stored proc? > > > PROCEDURE SP_PurgeTxn (p_date IN DATE) > IS > BEGIN > LOOP > DELETE FROM detail_txn dt > WHERE tran_date = p_date > AND rownum <= 4000; > IF SQL%NOTFOUND THEN > EXIT; > ELSE > COMMIT; > END IF; > END LOOP; > COMMIT; > END; > >