Re: Query to find first missing integer in a field
From: ddf <oratune_at_msn.com>
Date: Wed, 8 Apr 2009 11:40:01 -0700 (PDT)
Message-ID: <effad46d-4d16-4083-a4a3-759f77c8703c_at_j12g2000vbl.googlegroups.com>
On Apr 8, 1:20 pm, DeanB <deanbrow..._at_yahoo.com> wrote:
> On Apr 8, 11:51 am, lothar.armbrues..._at_t-online.de (Lothar Armbrüster)
> wrote:
>
>
>
>
>
> > Mark D Powell <Mark.Pow..._at_eds.com> writes:
>
> > [...]
>
> > > 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 --
>
> > Here is another way that does not involve a second table. It makes smart use
> > of analytic functions:
>
> > select min(next_id)
> > from
> > (select
> > id+1 next_id,
> > lead(id,1) over (order by id)-id diff
> > from
> > test)
> > where
> > diff>1;
>
> > Be aware that this does a full table scan or a full index scan.
>
> > HTH,
> > Lothar
>
> > --
> > Lothar Armbrüster | lothar.armbrues..._at_t-online.de
> > Hauptstr. 26 |
> > 65346 Eltville |- Hide quoted text -
>
> > - Show quoted text -
>
> Another good example - thank you. Do we know for sure that in this
> query that Oracle cannot optimize this (so that it doesn't need a full
> scan?) Assume that there is an index of course, or that it uses an
> index.- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 8 Apr 2009 11:40:01 -0700 (PDT)
Message-ID: <effad46d-4d16-4083-a4a3-759f77c8703c_at_j12g2000vbl.googlegroups.com>
On Apr 8, 1:20 pm, DeanB <deanbrow..._at_yahoo.com> wrote:
> On Apr 8, 11:51 am, lothar.armbrues..._at_t-online.de (Lothar Armbrüster)
> wrote:
>
>
>
>
>
> > Mark D Powell <Mark.Pow..._at_eds.com> writes:
>
> > [...]
>
> > > 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 --
>
> > Here is another way that does not involve a second table. It makes smart use
> > of analytic functions:
>
> > select min(next_id)
> > from
> > (select
> > id+1 next_id,
> > lead(id,1) over (order by id)-id diff
> > from
> > test)
> > where
> > diff>1;
>
> > Be aware that this does a full table scan or a full index scan.
>
> > HTH,
> > Lothar
>
> > --
> > Lothar Armbrüster | lothar.armbrues..._at_t-online.de
> > Hauptstr. 26 |
> > 65346 Eltville |- Hide quoted text -
>
> > - Show quoted text -
>
> Another good example - thank you. Do we know for sure that in this
> query that Oracle cannot optimize this (so that it doesn't need a full
> scan?) Assume that there is an index of course, or that it uses an
> index.- Hide quoted text -
>
> - Show quoted text -
If you read Lothar's post again you'll see he does state it could use an index scan:
> > Be aware that this does a full table scan or a full index scan.
and it could, provided the index is on the ID column (whatever you may have named it) and that the column is declared as NOT NULL. If the column can contain NULL values then it won't matter if there is an index or not, Oracle will perform a full table scan for that query.
David Fitzjarrell Received on Wed Apr 08 2009 - 13:40:01 CDT