Skip to Main Content
  • Questions
  • Impact of Altering an Oracle sequence from ORDER to NO ORDER

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sachi.

Asked: November 13, 2018 - 9:55 am UTC

Last updated: February 02, 2021 - 5:10 pm UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

I have a table TAX_INFO where the primary key TAX_INFO_ID is generated using a sequence SEQ_TAX_INFO_ID. Below is the sequence definition. It is defined as ORDERED at the moment

CREATE SEQUENCE  SEQ_TAX_INFO_ID  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 10000 CACHE 5000 ORDER  NOCYCLE


Currently the Column TAX_INFO_ID is used in many queries involving ORDER BY , MAX, MIN clauses as a part of business logic.

As we have moved to RAC architecture recently , there is a requirement to alter the sequence to NO ORDER so that the sequence usage can be improved.

The question is , Will there be any impact on the existing queries that uses the TAX_INFO_ID in ORDER BY , MAX, MIN clauses ?


and Chris said...

When using RAC, using noorder will mean each instance is allocated its own set of values.

So to begin with

Instance 1 gets 1 - 20
Instance 2 gets 21 - 40
Instance 3 gets 41 - 60
etc.

When an instance "uses" up its values, it'll get the next available batch. So if you have the three instances above, whichever hits its limit first will get the values 61-80.

So if these values go in TAX_INFO_ID, and you're sorting by it, the rows will NOT appear in the order you inserted them.

But!

Even with a single instance, ordered sequence, there's no guarantee the TAX_INFO_ID values match the insert order!

You can see this as follows. First create a table and sequence:

create sequence s order;
create table t (
  c1 int primary key,
  c2 date default sysdate
);


And in one session run:

declare
  id int := s.nextval;
begin

  dbms_lock.sleep ( 10 ) ;
  
  insert into t 
    values ( id, sysdate );
 
  commit;
end;
/


And another do:

begin

  insert into t 
    values ( s.nextval, sysdate );
    
  commit; 
  
end;
/


So the first session gets the sequence value. Then waits 10s before inserting it. The second inserts it immediately.

Which means the C1 values are "out-of-sequence":

select c1, to_char ( c2, 'hh24:mi:ss' ) 
from   t
order  by c2;

C1   TO_CHAR(C2,'HH24:MI:SS')   
   2 08:29:34                   
   1 08:29:41    


So if you're relying on the sequence values to be the order you inserted rows you already have a bug!

It might be rare you hit this. But it can still happen.

Rating

  (2 ratings)

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

Comments

Ordered sequence RAC environment

bc, February 01, 2021 - 3:07 pm UTC

One of our legacy application uses ordered sequences, that worked without any major issues ( although performance as not the greatest ), on a single instance 12c database,

Our future plan is to migrate to 19 / 20 / 21 c on a Exadata RAC, RAC being the biggest concern here, I have noticed uncached and ordered sequences perform terribly on RAC databases.

What options are available to get an ordered sequence value ( or an ordered unique value ) while not impacting performance in a RAC environment. ( Yup, I want to have my cake and eat it too ... )

Thank you
BC


Connor McDonald
February 02, 2021 - 1:26 am UTC

Any reason you can't go with cached ordered ? The lack of caching hurts a lot more than ordering.

Ordered sequence RAC environment

bc, February 02, 2021 - 2:49 pm UTC

Well that is the tricky part, this is a legacy application, the teams ( Business and IT ) that support it believes that if the transactions are not recorded in the order in which they are received, then a great chasm will appear and swallow the entire world ... yup ...

I understand the impact of not caching and ordering a sequence, I'd like to know if there is an alternative that will be a good compromise.
Chris Saxon
February 02, 2021 - 5:10 pm UTC

transactions are not recorded in the order in which they are received

So you've got created/received timestamp columns on the appropriate tables then, right? Otherwise there's really no way to know!

Ultimately any method which tries to enforce ascending (id) values will run into scalability issues.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.