Re: Select Distinct over Database Link

On Tue, 20 Aug 2002 19:45:19 +0400, "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote:

><Kenneth Koenraadt> wrote in message news:3d625712.3713780_at_news.capgemini.se...
>
>> Those "intermediate" commits, as you call them, are just good practice
>> when inserting > 1M records. Nothing to do with the problem itself.
>
>Why are they good practice for >1M rows? Which resources will they
>save/release? Which problems they will help to avoid when issued in
>a loop over a cursor? Does your statement mean that they are not a
>good practice for <1M rows? Please clarify.
>

Still has nothing to do with the original subject, but OK :

My statement regards the processing of a large number of records. "Large number" is relative in the sense that 1M inserts/updates may be a considerable number on a small system, but no problem in a larger system.

The advantages of committing with a certain frequency :

1. Prevents rollback segment from growing enormously. With inserts, as in this case, that is not the biggest problem.
2. The possibility of easily auditing the job from another session, i.e. counting the number of records currently inserted by the job and from that estimate i.e. the time of job completion.
3. Less rollback is needed in case of job failure.
4. The commit freqency can be varied. In the actual case, you could rewrite the block as a named procedure which takes commit_frequency as a parameter. That parameter could even have a default of, say, -1, which would mean "no commit before all inserts are done".
   • Kenneth Koenraadt

>--
>Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
>Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
>All opinions are mine and do not necessarily go in line with those of my employer.
>
>
Received on Wed Aug 21 2002 - 02:52:05 CDT