Impact of Altering an Oracle sequence from ORDER to NO ORDER

user_5848409 0
Viewed 213

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 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 this TAX_INFO_ID is used in many queries involving order by , MAX, MIN clauses.

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

Will there be any impact on the existing queries that uses the TAX_INFO_ID in order by , MAX, MIN clauses ?

oracle11g sql-order-by plsql oracle
1 Answers

There can/will be impact because NOORDER doesn't guarantee order

Therefore you should use different column of date/timestamp type (insert/update/end time) for guaranteed order by or min/max function.

NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

user_72949000
Related