Re: generating pk for millions of recs...

Hi all,

Thanks a lot for your helpful response :-) Using the sequence with a cache number nearly the same as the daily amount of records and getting rid of the Trigger work perfectly without sacrificing performance at all 4 minutes as before no ids.

Thanks again,
Best regards,
Giovanni

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:mtK8a.343119$Ec4.343809_at_rwcrnsc52.ops.asp.att.net...
> No sequences will be much faster and your max val won't work. Increase
the
> size of your sequence cache from 0 or 1 to 10000 and see if that helps
(bet
> it does). Als do as Niall sugests and parellilze the load if possible.
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
figure
> it out.
> "Giovanni Azua" <bravegag_at_hotmail.com> wrote in message
> news:b3v8ci$1qhlda$1_at_ID-114658.news.dfncis.de...
> > Hello all,
> >
> > I have a batch process which builds time-series aggregations
> > over million of records each loading time (daily), in order to
> > cluster those new generated aggregation time-series records I
> > needed to provide a primary key and I was really surprised by
> > the result, several times slower, the process without pk took 4
> > minutes, and with pk generation using sequence and trigger 35
> > minutes!!!!.
> >
> > That means the calling of the trigger is killing performance (I also
> > tried creating an adhoc function getting exactly the same result). I
> > suspect that the slowdown is due to the fact that sequences
> > implement synchronization among sessions since here will always
> > be one session calling this batch loading process I could get rid of
> > this contention problem? or using something like:
> >
> > insert into my_table
> > select nvl(max(id), 1) as id
> > ... // remaining columns...
> >
> > should be faster and more appropriate in this case than sequence and
> > trigger, right?
> >
> > TIA,
> > Best Regards,
> > Giovanni
> >
> >
>
>
Received on Mon Mar 03 2003 - 09:31:16 CST