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 es*****@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