In most of application databases, we can find one or more numeric columns representing IDs or used as primary keys. Usually, those columns would store sequential values. In Oracle DBMS, sequential values can be generated using Sequences. But it is also common to find in the applications layer some special logic for ID generation. No matter if one uses Sequences or not, it is somewhat common to find gaps (due to runtime errors or to a problem in the app logic) in this kind of field. In the following, we will discuss how to use recursive queries to list those gaps (there are other functions that can be used, like Lag and Lead, but we will present them here).
Suppose a Customer_ID column that stores a sequential number generated by the application layer. Unfortunately there are some gaps in the stored values and you want to list those missing IDs. The first step is to build a list of the numbers that should be stored in the table. This can be done using a recursive query just like the following.
The above query lists all possible values between 1 and the highest value stored in
Customer_ID (in the example, that would be 15). If you want to use another value as upper bound, just replace the subquery for such upper bound value.
Now, we can use the list of numbers to look for the missing IDs in the Curstomers table, using the query in the following figure. In such query, the List subquery generates all the values between 1 and the highest Customer_ID and the Not Exists clause is used to look for the values that are missing in the Customers table.
In the above example, there are 4 numbers missing in the Customer_ID column.
But don’t forget to look into your ID generation logic in order to reduce the generation of gaps!