generating pk for millions of recs...

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 - 03:45:26 CST