Skip to Main Content
  • Questions
  • Difference between named sequence and system auto-generated

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: November 12, 2018 - 4:03 pm UTC

Last updated: October 01, 2024 - 5:16 pm UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello, Guys.

A new db (12c) will have lots of tables with sequence used as PK.

What is the difference between named sequence and system auto-generated in Oracle 12c?

What would be the best approach?

and Chris said...

I'm assuming you're talking about the sequences you get with identity columns by auto-generated.

If so, the key difference is the identity sequence "belongs" to the table. The database creates/drops it with the table:

create table t (
  c1 int generated as identity
);

select column_name, data_default 
from   user_tab_cols
where  table_name = 'T';

COLUMN_NAME   DATA_DEFAULT                      
C1            "CHRIS"."ISEQ$$_147344".nextval  

drop table t cascade constraints purge;

select last_number from user_sequences
where  sequence_name = 'ISEQ$$_147344';

no rows selected


It also "just works" when used with other features, such as Data Pump. See:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9539308000346935551

So if you're only using a sequence to supply primary key values, it's generally easier to go with identity.

If you want to do something more exotic - say use the same sequence to set values for many tables - a regular sequence may be best. I'm struggling to think of a time when this would have been useful for me though.

Rating

  (6 ratings)

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

Comments

Review

Geraldo Peralta, November 12, 2018 - 8:36 pm UTC

Thanks.

I am using -let's say- 20 sequences for 20 tables.

So if you're only using a sequence to supply primary key values, it's generally easier to go with identity.

1. When couldn't this be easier? Or it's just up to the DBA select one or another?

2. What do you recommend?



Chris Saxon
November 13, 2018 - 11:34 am UTC

1. When couldn't this be easier? Or it's just up to the DBA select one or another?

Like I said, it would be something like using the sequence for something other than providing PK values for one table. e.g. if you want to share one sequence between many tables.

I can't think of a good reason why you'd want to do that though ;)

2. What do you recommend?

What I said:

So if you're only using a sequence to supply primary key values, it's generally easier to go with identity.

If you're using the sequences for some other reason, you (probably) need to stick with regular sequences.

Review

Geraldo Peralta, November 13, 2018 - 12:44 pm UTC

Thanks.

It's just for PK. No other use.

Something else

Geraldo Peralta, November 13, 2018 - 1:09 pm UTC

I just realized that I cannot alter a system-generated sequence. The following error is thrown: ORA-32793 error “cannot alter a system-generated sequence”.

How can I change the cache, fox example, after a table is created with identity?

If this must be specified just from the beginning I prefer to use a regular sequence. Because I do not know if I have to change some sequence properties later on.

What do you thing?
Chris Saxon
November 13, 2018 - 3:00 pm UTC

You can do this by modifying the column and setting the identity properties:

create table t (
  c1 int generated as identity
);

select cache_size, increment_by 
from   user_sequences
where  sequence_name like 'ISEQ$$%';

CACHE_SIZE   INCREMENT_BY   
          20              1 

alter table t 
  modify c1 
    generated as identity 
    increment by 2
    cache 1000;
    
select cache_size, increment_by 
from   user_sequences
where  sequence_name like 'ISEQ$$%';

CACHE_SIZE   INCREMENT_BY   
        1000              2 

Review

Geraldo Peralta, November 13, 2018 - 3:09 pm UTC

Great! You're awesome!

Thanks. :)
Chris Saxon
November 13, 2018 - 4:46 pm UTC

Happy to help

NEXTVAL, CURRVAL and last_number confustion

t1dsoldier, October 01, 2024 - 1:24 pm UTC

Hello Tom,

Wasnt sure if this needed a new post or if it fits here.
CAT_TX_QUEUE_SEQ_ID is an identity column sequence but I also noticed this same behavor in another sequence the app team asked me to modify. I used alter sequence in that situation.

I dont know a lot about sequences so my assumption could be wrong, but shouldnt CURRVAL and LAST_NUMBER match or at least LAST_NUMBER be 1 number behind?
I cant give full DDL of the table because it is a prod table but this is most of it
CREATE TABLE TEST.CAT_TX_QUEUE
(
CATALOG_TX_QUEUE_SEQ_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH 999 MAXVALUE 9999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER NOKEEP NOSCALE) CONSTRAINT CAT_TX_QUEUE_NN1 NOT NULL,
SOURCE_TABLE_NM VARCHAR2(30 BYTE) CONSTRAINT CAT_TX_QUEUE_NN2 NOT NULL,
PRIMARY_KEY VARCHAR2(60 BYTE) CONSTRAINT CAT_TX_QUEUE_NN3 NOT NULL

)

PARTITION BY RANGE (CAT_TX_QUEUE_SEQ_ID)
INTERVAL (250000)
(
PARTITION CATTXQ_PART_01 VALUES LESS THAN (250000)
)
NOCACHE;

CREATE UNIQUE INDEX TEST.CAT_TX_QUEUE_PK ON TEST.CAT_TX_QUEUE
(CATALOG_TX_QUEUE_SEQ_ID);

ALTER TABLE TEST.CAT_TX_QUEUE ADD (
CONSTRAINT CAT_TX_QUEUE_PK
PRIMARY KEY
(CAT_TX_QUEUE_SEQ_ID)
USING INDEX TEST.CAT_TX_QUEUE_PK
ENABLE VALIDATE);


-- Sequence ISEQ$$_1206002 is created automatically by Oracle for use with an Identity column




SQL> alter table TEST.CAT_TX_QUEUE
2    modify CAT_TX_QUEUE_SEQ_ID
3     generated as identity
4*   start with  997;

Table TEST.CAT_TX_QUEUE altered.

SQL> commit; <== dont think its needed but just in case

Commit complete.


SQL> SELECT ISEQ$$_1206002.nextval
2*   FROM DUAL;


  NEXTVAL
__________
      997


SQL> SELECT ISEQ$$_1206002.currval
2*   FROM DUAL;

  CURRVAL
__________
      997


SQL> select sequence_owner, sequence_name, last_number from dba_sequences where sequence_name = 'ISEQ$$_1206002';

SEQUENCE_OWNER    SEQUENCE_NAME        LAST_NUMBER
_________________ _________________ ______________
TEST            ISEQ$$_1206002               998 <== shouldn't this be 997



SQL> SELECT ISEQ$$_1206002.nextval
2*   FROM DUAL;

  NEXTVAL
__________
      998


SQL> SELECT ISEQ$$_1206002.currval
2*   FROM DUAL;

  CURRVAL
__________
      998



SQL> select sequence_owner, sequence_name, last_number from dba_sequences where sequence_name = 'ISEQ$$_1206002';

SEQUENCE_OWNER    SEQUENCE_NAME        LAST_NUMBER
_________________ _________________ ______________
TEST            ISEQ$$_1206002               999 <== shouldn't this be 998


SQL>
Chris Saxon
October 01, 2024 - 1:33 pm UTC

shouldnt CURRVAL and LAST_NUMBER match or at least LAST_NUMBER be 1 number behind?

No.

From the docs:

Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

e.g. if you create a sequence starting with 1 and a cache of 20 (the default), then get nextval, last_value in the data dictionary is updated to 1 + 20:

create sequence s;
select s.nextval from dual;
select last_number from user_sequences
where  sequence_name = 'S';

LAST_NUMBER
-----------
         21

thanks so much

t1dsoldier, October 01, 2024 - 2:53 pm UTC

they should allow thumbs up or something.

thanks for the fast response that helps a lot.
Chris Saxon
October 01, 2024 - 5:16 pm UTC

You're welcome - you can just leave a five star review if you're happy ;)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.