2

When I am generating sequence values from the below-created sequence trial_seq, it gave 1, 2, 21, 41, 4,......

CREATE SEQUENCE trial_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

I am confused with the working of CACHE. What values from the sequence are stored in cache when for the first time NEXTVAL is called (just after the creation of sequence)? Are they from 1 to 20 (both inclusive) or just some random 20 numbers between MINVALUE and MAXVALUE? Now, if the cache is storing random 20 number within sequence range, then it's okay but if the cache is storing from 1 to 20, then why it is giving 21 and subsequently 41, it should be giving values within the range of 1 to 20 until all values within this get exhausted? I specifically want to understand this by NOT using NOCACHE and/or ORDER. Also, I am just learning, not using for RAC.

saint_sharan
  • 119
  • 1
  • 12
  • 1
    Are you sure you aren't using RAC? That looks like what you'd see with a separate cache of 20 values in each instance of a 2-node cluster. If not, how are you calling nextval and seeing the numbers in that order; maybe it's part of an insert, and when you query the table you're seeing them in an order that just doesn't match the sequence? – Alex Poole Aug 30 '20 at 18:10
  • @AlexPoole actually I'm practicing on Oracle Apex. And when I started to see the values by executing "SELECT trail_seq.NEXTVAL FROM DUAL;" repeatedly, I struck with this problem. – saint_sharan Aug 30 '20 at 18:38

1 Answers1

2

You describe that you get a sequence value in the following order 1, 2, 21, 41, 4. Are you executing the nextval query from within APEX? That might indeed be correct.

APEX uses connection pools and a sequence cache cannot be shared over connections, so each connection will have its own sequence cache.

Let's imagine you have a connection pool of 5 connections. Each connection will create its own sequence cached values at the moment you execute sequence.nextval within that connection.

Connection 1 - Sequence cache 1 - 20
Connection 2 - Sequence cache 21 - 40
Connection 3 - Sequence cache 41 - 60
Connection 4 - Sequence cache 61 - 80
Connection 5 - Sequence cache 81 - 100

You can imagine that when APEX controls which connection it is going to use within the connection pool, it is impossible to determine the sequence onbeforehand.

In short:

  • The sequence will generate a number in ascending order, f.e. 1,2,3,4,5,etc.. not random.
  • There can be gaps within that sequence, f.e. 1,2,4,5,8,9,10. Here 3,6,7 are skipped and will never be used again.
  • If you run it through APEX, multiple connections can/will be used and so multiple cached sequence ranges will be used. The order you will see could be anything like 1,21,41,22,42,2,3,43. The jumping from 1 to 21 to 41, back to 22 aren't gaps, but is because it is using a different connection, and thus a different cached sequence range. I used 1, 21, 41 so you can see the CACHE 20 behaviour back in the example.

You could try executing this as a script, to see the correct behaviour:

DROP SEQUENCE trial_seq
/

CREATE SEQUENCE trial_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20
/

create table trial_test( id number)
/

insert into trial_test
select trial_seq.nextval
from   dual
connect by rownum <= 10
/

select *
from   trial_test
/

What is the outcome? It should be 1 - 10, or at least somewhere near that outcome.

Let me know if this answers your question.

Giliam
  • 608
  • 3
  • 15
  • That's an informative comment. But I was asking something different. When we use cache 20, does apex is supposed to output 20 numbers from 1-20 (ordering is not necessary), before giving numbers greater than 20? Or it's like the cache stores values some random 20 numbers (which include 1, 2, 21, 41, 4,...) and from this cached random numbers, it is giving out the values? Or, is there any other way the cache is producing numbers? (NOTE:- I'm supposing that I'm using sequence generation just after its creation) – saint_sharan Sep 01 '20 at 03:46
  • Updated my answer. If APEX uses one connection, the output of the sequence would be from 1-20 in the ascending order. However gaps can exist, but not jumping back.. The jumping happens because of the connection pool APEX uses. Does that make sense to you? – Giliam Sep 01 '20 at 07:43