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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

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

Last updated: November 13, 2018 - 4:46 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

  (4 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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.