Re: Locking a Table Within an INSERT
From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Wed, 07 Jul 2004 17:12:11 +0100
Message-ID: <40EC20DB.7020600_at_orindasoft.com>
>
>
> Would that I could. Unfortunately, the business requirements are that
> I know the generated values in advance of inserting them.
>
> I've thought of establishing an intermediate table into which I could
> insert the values from a sequence, together with other record
> identifying information, to be used in a lookup later. But I'm dealing
> with, in some cases, a couple of million rows per run, with a dozen
> runs a day.
>
> You can write me at eshevin_at_ford.com if you'd care to hear more.
Date: Wed, 07 Jul 2004 17:12:11 +0100
Message-ID: <40EC20DB.7020600_at_orindasoft.com>
Elliott wrote:
>>Use a sequence >>replace the horrible expression by >><sequence>.nextval and you are set. >> >>Sybrand Bakker >>Senior Oracle DBA
>
>
> Would that I could. Unfortunately, the business requirements are that
> I know the generated values in advance of inserting them.
>
> I've thought of establishing an intermediate table into which I could
> insert the values from a sequence, together with other record
> identifying information, to be used in a lookup later. But I'm dealing
> with, in some cases, a couple of million rows per run, with a dozen
> runs a day.
>
> You can write me at eshevin_at_ford.com if you'd care to hear more.
<Various Random Ideas>
Have you thought about:
- Accessing a sequence once per run and adding 8 zeros to the end of the number you get. Then add the number of the record to get a unique identifer that is derived from a sequence. You could also use a sequence that increments by 10 million each time. Oracle won't be phased by this but check the size of the numeric data types used by non-oracle languages to access the data.
- Using a numeric identifier that has decimal places. The left hand half identifies the number of the row in the batch and the right hand half is pulled from a sequence once at the start of the process.
- splitting the PK into two - a batch number (pulled from sequence like above) and a number within a batch
</Various Random Ideas>
David Rolfe
Orinda Software
Dublin, Ireland
Received on Wed Jul 07 2004 - 18:12:11 CEST