Thanks for the question.

Asked: April 15, 2014 - 1:48 pm UTC

Last updated: May 10, 2018 - 8:00 am UTC

Version: 10g

Viewed 1000+ times

You Asked

hi tom,

during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextval.how will i get it ?? please suggest me.

and Tom said...

well, my answer would have been "why"?

I cannot think of any more efficient approach than an alter, followed by select, followed by alter.

Ok, here is one:

drop sequence s;
create sequence s start with 20000;

It does not execute a select at all, so that is less than 20,000 executions. It does not alter the sequence either.

or here is a way that meets the letter of their specification:

ops$tkyte%ORA11GR2> with data
  2  as
  3  (select level l from dual connect by level <= 20000)
  4  select s.nextval seq from data;

       SEQ
----------
         1
         2
...
     19998
     19999
     20000

20000 rows selected.

I only executed on select statement....

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 16, 2014 - 6:37 am UTC

thanks for your quick response tom.
but my question was different without alter is it possible to get nextval 20000 after the nextval 20 ?

April 16, 2014 - 4:28 pm UTC

sure, use 20000-20 instead of 20000???....

did you understand what I typed above? how it works? if you did - you'd be able to modify it to meet any need....

the drop+create would do it.

Mohd Sirajoddin, April 16, 2014 - 9:59 am UTC

The Answer by Tom is very clear, and the answer is perfect. The Interviewer asked you this question just to confuse, and to findout the way you mindset works..

Another possibility

Falco, April 16, 2014 - 3:38 pm UTC

One possibility is Sequence Caching - you can set a Sequence cache for Multithreading (Tom wrote it somewher in another question) - So each time a User accesses the Sequence it automatically allocates a certain number of Values on the sequence.

If you set this Sequence caching to 19980 one User will get NEXTVAL => 20 and the next User to log in will get NEXTVAL => 20000 (Or you log out, log in yourself)

So you don't even have to drop or alter the sequence, just get the DB to drop the preallocated 19980 values

April 16, 2014 - 4:36 pm UTC

you'd have to alter the sequence however, violation of the problem statement...

You'd need RAC to get the sequence caching to work for you though (or a shutdown abort).

In order for sequence caching to do it - you'd have to increase the cache value, select from it on node 1 (they would generate 20...19999 now). Then select from it on node2 - they would generate 20000..20000+19980...

on a single instance, set the cache up - select nextval - shutdown abort (that should "lose" the cached values). upon startup it would pick up at 20,000

better way if you are going to alter is

alter the increment.
select it (it'll be 20k)
alter the increment back to 1.

alter start number

Michael, April 23, 2014 - 7:21 am UTC

short question. why didn't Oracle implement the possibility to just alter the start number, eg. alter sequence s start with 20000?

Sequence

subrat ray, April 24, 2014 - 7:24 am UTC

CREATE SEQUENCE S
START WITH 1
INCREMENT BY 1
MAXVALUE 40
NOCACHE
NOCYCLE;

after getting the sequnce value 20 i execute the below query and it is giving the desired output.

SELECT (S.NEXTVAL -1 )*1000 FROM DUAL;

Scalable sequences in 18c

Rajeshwaran, Jeyabal, May 09, 2018 - 7:37 am UTC

Team,

Was reading through the scalable sequence from Richardfoote blog.

https://richardfoote.wordpress.com/2018/05/08/18c-scalable-sequences-part-iii-too-much-rope/

dont have access to 18c, so the below demo is from 12.2

setting noscale sequence, doesn't alter sequence back to normal state. is it not possible to alter the scalable sequence ?

demo@ORA12C> create sequence s scale;

Sequence created.

demo@ORA12C> select scale_flag from user_sequences where sequence_name ='S';

S
-
Y

demo@ORA12C> select s.nextval from dual;

                             NEXTVAL
------------------------------------
        1013890000000000000000000001

demo@ORA12C> alter sequence s noscale;

Sequence altered.

demo@ORA12C> select scale_flag from user_sequences where sequence_name ='S';

S
-
N

demo@ORA12C> select s.nextval from dual;

                             NEXTVAL
------------------------------------
        1999990000000000000000000002

demo@ORA12C> declare
  2     x int;
  3  begin
  4     for i in 1..1000
  5     loop
  6             x := s.nextval;
  7     end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

demo@ORA12C> select s.nextval from dual;

                             NEXTVAL
------------------------------------
        1999990000000000000000001003

demo@ORA12C>

May 10, 2018 - 8:00 am UTC

I would not make sense (in my opinion) for us to automatically reset the sequence numbers because it could lead to clashes with existing "used" values.

All that the scale attribute is doing is controlling the ranges that are built - but it is still just a "normal" sequence, so if you 'noscale' it, then we continue on from where we left off.

Once noscaled, you can reset it using the negative increment method as we've always had, eg

SQL> drop sequence s;

Sequence dropped.

SQL>
SQL> create sequence s scale;

Sequence created.

SQL>
SQL> select s.nextval x from dual;

                                X
---------------------------------
     1010790000000000000000000001

1 row selected.

SQL>
SQL> alter sequence s noscale;

Sequence altered.

SQL>
SQL> select scale_flag from user_sequences where sequence_name ='S';

S
-
N

1 row selected.

SQL>
SQL> select s.nextval x from dual;

                                X
---------------------------------
     1999990000000000000000000002

1 row selected.

SQL>
SQL> alter sequence s increment by -999990000000000000000000000;

Sequence altered.

SQL>
SQL> select s.nextval x from dual;

                                X
---------------------------------
     1000000000000000000000000002

1 row selected.

SQL>
SQL> alter sequence s increment by -1000000000000000000000000000;

Sequence altered.

SQL>
SQL> select s.nextval x from dual;

                                X
---------------------------------
                                3

1 row selected.

SQL>
SQL> alter sequence s increment by 1;

Sequence altered.

SQL>
SQL>
SQL> select s.nextval x from dual;

                                X
---------------------------------
                                4

1 row selected.

SQL> select s.nextval x from dual;

                                X
---------------------------------
                                5

1 row selected.

SQL>

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer