在oracle中生成6位唯一随机数生成器序列 [英] Generating 6 digit unique random number generator sequence in oracle

问题描述

我尝试在 oracle SQL 中使用 noorder 子句，但我仍然以升序获取生成的序列.

I have tried using the noorder clause in oracle SQL but still I am getting the generated sequence in ascending order.

下面是序列创建脚本

create sequence otp_seq 
    minvalue 100000 maxvalue 999999 
    increment by 1 nocycle noorder;

当我重复运行以下命令时:

When I run the below command repeatedly:

select otp_seq.nextval from dual;

它只给出序列中的值:

100000
100001
100002

我想要的是从给定域中随机生成的值，即在 minValue 和 maxValue 之间，并且应该是唯一的.

What I want is values to be generated randomly from the given domain i.e. between the minValue and maxValue and should be unique.

推荐答案

关于 NOORDER 子句，文档说:

如果您不想保证按请求顺序生成序列号，请指定NOORDER."

"Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. "

关键词是保证.NOORDER 不保证随机性，这意味着 NEXTVAL 可能会产生乱序的数字.这主要在 RAC 环境中受到关注，其中每个节点都有一个序列号缓存；在这些场景中，NOORDER 意味着我们无法从给定值的序列推断 NEXTVAL 请求的序列，即我们无法使用这些数字按创建顺序对记录进行排序.

The key word is guarantee. NOORDER does not promise randomness, it means NEXTVAL may generate numbers out of order. This is primarily of concern in RAC environments where each node has a cache of sequence numbers; in these scenarios NOORDER means we cannot infer sequence of NEXTVAL requests from the sequence of given values i.e. we cannot use those numbers to sort records in order of creation.

根据您的要求.

您的要求是矛盾的.随机性意味着不可预测性.唯一性意味着可预测性.

Your requirements are contradictory. Randomness means unpredictability. Uniqueness means predictability.

你不能用一个序列来实现它，但你可以像这样构建你自己的东西:

You cannot implement this with a sequence but you could build you own thing like this:

create table pseudo_sequence (
    used varchar2(1) default 'N' not null
    , id number not null
    , next_val number not null
    , primary key (used, id)
    )
organization index
/

注意仅索引表语法.下一个技巧是随机填充表.

Note the Index Only Table syntax. The next trick is to populate the table randomly.

insert into pseudo_sequence (id, next_val)
with nbr as (
    select level + 99999 as nx
    from dual
    connect by level <= 900000
    order by dbms_random.value
  )
select rownum, nx from nbr
/   

我们需要 ID 列来保留 NEXT_VAL 在整个表中的随机分布；如果没有它，索引将强制执行顺序，我们希望避免每次进行查询时都进行排序.

We need the ID column to preserve the random distribution of NEXT_VAL across the table; without it the index will impose an order, and we want to avoid sorting every time we do a query.

接下来我们构建一个查询以从表中获取下一个值，并将其标记为已使用:

Next we build a query to get a next value from the table, and mark it as used:

create or replace function random_nextval
    return number
is
    pragma autonomous_transaction;
    cursor ps is
        select next_val 
        from pseudo_sequence
        where used = 'N'
        and rownum = 1
        for update of used skip locked;
    return_value number;
begin
   open ps;
   fetch ps into return_value;
   update pseudo_sequence
   set used = 'Y'
   where current of ps;
   close ps;
   commit;
  return return_value;
end;
/

这是它的工作原理:

SQL> select random_nextval from dual
  2  connect by level <= 5
  3  /   

RANDOM_NEXTVAL
--------------
        216000
        625803
        806843
        997165
        989896

SQL> select * from pseudo_sequence where used='Y'
  2  /

U         ID   NEXT_VAL
- ---------- ----------
Y          1     216000
Y          2     625803
Y          3     806843
Y          4     997165
Y          5     989896

SQL> select random_nextval from dual
  2  connect by level <= 5
  3  /

RANDOM_NEXTVAL
--------------
        346547
        911900
        392290
        712611
        760088

SQL>

当然，我们可以争辩说这不是随机的，因为通过查看基础表可以预测下一个值，但也许它足以满足您的需求.我不会对多用户环境中的可扩展性做出任何承诺，但鉴于您的数字空间只有 900,000 个值，我认为这无论如何都不是主要问题.

Of course, we could argue this is not random as the next value is predictable by looking at the underlying table but perhaps it's good enough for your needs. I won't make any promises about scalability in a multi-user environment, but given your numberspace is a scant 900,000 values I figure that's not a major concern anyway.

这篇关于在oracle中生成6位唯一随机数生成器序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！