Re: Query to find first missing integer in a field
- From: Mark D Powell <Mark.Powell@xxxxxxx>
- Date: Wed, 8 Apr 2009 05:43:02 -0700 (PDT)
On Apr 7, 4:38 pm, DeanB <deanbrow...@xxxxxxxxx> wrote:
On Apr 7, 4:08 pm, ddf <orat...@xxxxxxx> wrote:
On Apr 7, 2:51 pm, dean <deanbrow...@xxxxxxxxx> wrote:
Does anyone know a good way to find the first free integer number that
does not exist in a table in a specified column? I can think of a
couple of tedious ways of doing this but they are hardly elegant.
Assume there could be any integer present up to 2 billion.
This has 'DON'T DO THIS' written all over it in large, bold, red
letters as it's a disaster in the making. I suppose you're looking to
'backfill' missing sequence numbers and that is a recipe for failure.
Seriously rethink this before you're in too deep and end up with an
application that won't run because it won't scale.
David Fitzjarrell
Correct - the problem is that our consultants tend to poke around in
the database and analyse data, and its far easier to do this if the ID
numbers are 1234 rather than 158943758.
Also, sequences are not reproducable, so debugging a series of SQL
statements is sometimes a difficult process.
Why is 1234 easier than 158943758 ? Once you get into having millions
of rows what difference does it make? Most of the analysis should be
done via code which finds and uses the keys in question so no human
interventions should be necessary. Where the work is done manually
copy and paste comes to mind and well as a little pl/sql to perform
the necessary queries based off a short list of input values.
Here is one way to find the missing keys
--
-- select complete_range_of_interest (query to generate 1..N - many
examples on web)
-- from source_with_enough_rows
-- where range >= start_of_interest and range <=
max_value_of_interest (filter for min and max values in table)
-- minus
-- select values from table_of_interest
--
Be advised if you plan to update existing key to lower skipped values
that you have two issues that you may have to deal with: 1- if there
are FK relationships to the value you have to update the child
tables. If the FK relationship is not defined in the database but
only in the application you may easily miss a necessary update, and 2
- if you have any purge and archive processes then by re-using a key
you make restoration of archive data either impossible or create false
relationship between rows of data by restoring the reused key.
HTH -- Mark D Powell --
.
- Follow-Ups:
- Re: Query to find first missing integer in a field
- From: Lothar Armbrüster
- Re: Query to find first missing integer in a field
- From: DeanB
- Re: Query to find first missing integer in a field
- References:
- Prev by Date: Re: UNDO: 10g-style behaviour in 9.2.0.8?
- Next by Date: Re: Query to find first missing integer in a field
- Previous by thread: Re: Query to find first missing integer in a field
- Next by thread: Re: Query to find first missing integer in a field
- Index(es):
Relevant Pages
|
