CODEWITHSUNDEEP
sqloracle-databasesequence
ankit
ankit

Reputation: 61

Generating 6 digit unique random number generator sequence in oracle

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

Below is the sequence creation script

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;

it gives the values only in the sequence:

100000
100001
100002

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

Upvotes: 2

Views: 9392

Answers (1)

APC
APC

Reputation: 146239

Regarding the NOORDER clause, the documentation says:

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

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.

On to your requirements.

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
/

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;
/

And here is how it works:

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>

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.

Upvotes: 3

Related Questions