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 ?
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.