Finding Gaps of numbers within an Oracle Table

Today I needed to find a gap within a series of numbers located in a table. So for instance I have a table with a series of mslink values:

MSLINK

1

2

3

5

In the above table, I want to find the number 4. The solution is to use the "minus" query operator available from Oracle. Basically the minus operator takes the results of one query and gives you the values that do not exist in the second query. Here is the query:

select mslink+1 from mytable minus select mslink from mytable;

The trick here is the +1 for the mslink. When the database gets to the value of 3 in the table, it will add one making the value 4. It will then take the 4 and see if it exists in the second query. Since 4 does not exist in the table, it will return 4 as a gap which it correct! The query will probably do two full table scans but this is better than searching through the table yourself by fetching each row. I have not tested this on a table with millions of rows. My test example had about 4K rows and worked great! If I get around to testing this on a table with million+ rows, I'll update this blog.

If other gaps exist, they also will be returned by Oracle. If you want just one row returned, you could make a subquery of the above and use the rownum property. Here is an example:

select mslink from (select mslink+1 mslink from mytable minus select mslink from mytable) where rownum = 1;