Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: generating pk for millions of recs...
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...Received on Mon Mar 03 2003 - 09:25:06 CST
> 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
>
>