Oracle中如何查找表中最小ID可用值

Question

表名：BASIC 字段：ID 从1开始增加（非自动增加）的编号标志列，不可重复和空值，其他字段可重复可空值这里不考虑了； 现在的问题是：使用一段时间后如何找到表中最小可用ID值？请大家给出SQL语句，注意是单条SQL语句，不是让你写程序代码，也不要多条语句的结合。第一次发贴，虽然我已经摸索出一条方法，但是出于性能考虑，还是希望看到大家的更好语句出来，并互相探讨，共同进步。谢谢！

Answer 1

建议使用oracle序列,
自己找ID的SQL，如果在并发情况下，两个人同时执行，会得到一样的值，导致出错

Answer 2

不能用字查询和top-n分析吗？

Answer 3

你看看这个SQL。
我家里的电脑没有Oracle环境，不能测试，你自己试试吧Select (ID + 1) as NextID
  From (
        select * 
          from (
Select ID,RowNum as rowIndex
  From theTable
 Minus
Select ID,RowNum as rowIndex
  From theTable
 Where ID = RowNum
)
Order By ID Asc
)
Where Rownum = 1

Answer 4

TO:bjt_(bjt)选择使用序列是对的，但是现在讨论的不是选择的问题，而是怎么做的问题，谢谢。
TO：syily() 可以使用子查询，但是要求只是1条SQL语句
TO：qdragonballs(骑龙猪) 你的语句我还没有测试，今天星期6在家休息，没装ORACLE，等星期一我去测试再回复你

Answer 5

对，使用序列最方便了创建序列
create   sequence   id  --序列名     
increment   by   1     --每次增加1
start   with   1     --从1开始
nomaxvalue     --没有最大值
nocache        --没有缓存序列
;
查看下一个最小可用值　　select id.nextval from dual;

Answer 6

插入语句
insert into BASIC(ID)  values(id.nextval);

Answer 7

就是跳号的再利用，是吧?
你可以考虑按ID排序,然后找第一个ID与ROWNUM不等的记录,该记录的ROWNUM就是最小的被跳过的号...,例如:
select r
from
(
  select id, row_number() over(order by id) r from BASIC
)
where id <> r
  and rownum = 1

Answer 8

我那个SQL不大对，
Select ID,RowNum as rowIndex
  From theTable
 Minus
Select ID,RowNum as rowIndex
  From theTable
 Where ID = RowNum
这2个SQL应该先按ID排序再取ID和RowNum进行比较。当时忘了写了。。
-------------------------------------------------------------
你可以写一个函数，然后使用Select Function（……） as nextID From Dual 进行调用。

Answer 9

这个SQL我试过，应该满足你要求。
Select (ID + 1) as NextID 
  From (Select ID,RowNum as RowIndex
          From theTable
      Order By ID Asc)
 Where (ID > RowIndex Or ID < RowIndex)
   And rowNum = 1
我的theTable中的数据如下
ID
1
2
4
6
8
9
11
13
15

Answer 10

首先，不放在具体使用环境来考虑，任何求解都是有用的，所以这里不谈选择使用序列来同步ID好，还是单纯地求解最小ID值差； 其次，问题问的是求最小可用ID值，分两种情况，比如从1开始的编号，如果没有1存在就应该返回1，如果1存在就取其他最小ID值，这两种情况都应该考虑到；我总结的最简单的SQL语句是：Select rowindex From (Select NO, rownum rowindex from BASIC) where NO <> rowindex and rownum = 1；非常感谢 Mr. qdragonballs(骑龙猪)多次指教，但是有一点就是往往最简单的语句的执行效率最低，所以我觉得把帖子再押2天，希望大家针对性能提供更高效的语句，比如这个BASIC表中记录数约100000条，如果没有更好的答案，我将把分给 qdragonballs(骑龙猪) ，并结贴，希望大家多多指教，一起挑战这个难点。

Answer 11

对不起，犯了一个错误，SQL语句应该是：
Select rowindex From (Select NO, rownum rowindex from (SELECT NO FROM BASIC ORDER BY NO)) where NO <> rowindex and rownum = 1，为了测试语句的执行性能，我将使用程序代码向表BASIC（NO NUMBER(10),NAME VARCHAR2(20))中追加100000条记录，记录顺序为编号ID先从100000到2STEP-2也就是偶数，NAME设定值‘谢谢’进行追加，然后编号ID从1到99999STEP2也就是奇数，NAME设定值‘谢谢’进行追加，最后删除ID=2007的记录，请大家给出单条SQL语句，要求返回值是最小的可用ID值，也就是2007，我将给出处理时间，让大家进行公平评价，我同时将分值追加100，希望大家积极参与，所有分将送给处理时间最小者，谢谢大家！

Answer 12

恩,我觉得与其关注效率不如关注点儿别的问题...1,表为空时,这时候应该返回1,但上边的SQL应该是返回空吧;
2,表里虽有记录,但没有跳号的现象,也就是所有的ID都等于ROWNUM,这时候应该返回最大ID加1,但上边的SQL也是返回空;
3,就是并发了,一般的做法就是插入时发现已被别人抢先的话,就再取再插...

Answer 13

仔细分析，还是用我老早自己摸索的这条语句才行，郁闷，转了一圈，又回到原地了！！
SELECT (CASE WHEN (SELECT ID FROM (SELECT ID FROM BASIC ORDER BY ID) WHERE ROWNUM=1) <>1 THEN 1 ELSE (SELECT A.ID+1 FROM (SELECT ID FROM BASIC ORDER BY ID) A WHERE NOT EXISTS (SELECT ID FROM BASIC WHERE ID= A.ID+1) AND ROWNUM=1) END) 最小可用ID FROM DUAL

Answer 14

非常感谢 qdragonballs(骑龙猪) AND killlaoli(菜鸟) 的热心指导，收益非浅！但是没想到老早自以为自己摸索出的非常复杂的解决方案到最后却是最正确的。世事无常啊！！