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 - 11:12:11 CDT
