Re: Query to find first missing integer in a field
From: DeanB <deanbrown3d_at_yahoo.com>
Date: Wed, 8 Apr 2009 11:20:54 -0700 (PDT)
Message-ID: <8e89fe37-1486-4308-b0c6-dfade51f2d30_at_p11g2000yqe.googlegroups.com>
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 -
Date: Wed, 8 Apr 2009 11:20:54 -0700 (PDT)
Message-ID: <8e89fe37-1486-4308-b0c6-dfade51f2d30_at_p11g2000yqe.googlegroups.com>
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. Received on Wed Apr 08 2009 - 13:20:54 CDT