From: "Paul Brewer" Newsgroups: comp.databases.oracle.server References: <875e251b.0307070637.620d8c49@posting.google.com> Subject: Re: Intermittent Commits Date: Mon, 7 Jul 2003 20:26:43 +0100 Lines: 43 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 NNTP-Posting-Host: ppp-0-83.lond-a-2.access.uk.tiscali.com Message-ID: <3f09deec_1@mk-nntp-1.news.uk.worldonline.com> X-Trace: 7 Jul 2003 21:58:20 +0100, ppp-0-83.lond-a-2.access.uk.tiscali.com Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!kibo.news.demon.net!demon!mk-nntp-1.news.uk.worldonline.com!ppp-0-83.lond-a-2.access.uk.tiscali.com Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237091 "Michael" wrote in message news:875e251b.0307070637.620d8c49@posting.google.com... > Hye ! > > > I have a query which selects from several tables and inserts into a > table X. Two ways of writing this query are (A) fast but risky (B) > slow but certain > > > (A) Writing this select in one bulk INSERT SELECT statement will be > the fastest way, however we only commit once at the end. If something > wrong happens then nothing will be stored in the new table and in > addition we should wait a period of time for the database to rollback > its work. > > (B) Now performing the same INSERT in a cursor and COMMITing after > each INSERT is slower but more certain, because if something happens > which will stop > the query, then it is possible to restart from where we stopped. > > What I am looking for is a solution in between, that will balance > speed and risk for example to commit every chunk of records. The > reason for that is not space but time. When this query is run the > database should be turned offline and the downtime is minimal. By > introducing more commits, it will be much simpler to forecast how much > time will it take to perform this INSERT, better feedback of what is > happening. Any ideas?? > > I am using Oracle 8i and the INSERT varies from 100000 to 500000 > records. > The answer should be determined by the logical requirements, not the physical. If you want feedback on progress on an uncommitted long-running transaction, there are plenty of ways to provide that. But how long is a half-million row insert taking anyway? Regards, Paul