Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Snehasish.

Asked: October 08, 2015 - 7:57 am UTC

Last updated: July 31, 2017 - 2:53 am UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi ,

Good day.

We can have an alternative to sequence as below. This is quite similar to Identity column in SQL server.

CREATE TABLE identity_test_tab (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
description VARCHAR2(30)
);

I wanted to know the benefit of using this approach over using the sequence. I have been using sequences for these kind of operation for long and it would be a good idea to know the benefits and drawbacks of both these approaches so that I can use them in their appropriate situations to get the max out of the Oracle database.

Thanks in advance.

Snehasish Das.

and Connor said...

Well, for a start, I think you'll need to be on 12c before you can use them.

But other than that, the differences are only subtle. For example

SQL> drop table t purge;

Table dropped.

SQL> create table T 
  2    ( pk number generated as identity , 
  3      c1 int);
  
Table created.

SQL> select object_id, object_name, 
  2    object_type from user_objects;

OBJECT_ID  OBJECT_NAME        OBJECT_TYPE
---------- ------------------ ------------
414914     T                  TABLE
414915     ISEQ$$_414914      SEQUENCE


You can see that to perform the identity, Oracle created a sequence for you anyway, and you can control all the normal sequence functions at creation time, eg

SQL> create table T 
  2     ( pk number 
  3          generated as identity (cache 1000) 
  4      , c1 int);

Table created.


One thing this is perhaps of interest is that you also gain more control over what people try to do with the column - for example, you can "lock down" usage of the column:

SQL> insert into T values (1,2);

insert into T values (1,2)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


Hope this helps

Rating

  (4 ratings)

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

Comments

More on Sequence

Snehasish Das, October 08, 2015 - 11:14 am UTC

Hi,

Thanks a lot.

I am currently on 11gR2 but we are testing on 12C thats where the question came from.

I was more looking for scenarios where I can use Identity , i.e is it good to replace all tables which has ID populated by sequences to Identity.

Under which Scenarios would you ask your developers to use Sequence and not Identity and vice versa.

Regards,
Snehasish
Connor McDonald
October 08, 2015 - 11:57 am UTC

Its more of a migration issue (in my opinion).

If you already have developers familiar with sequences etc, then by all means keep that standard approach.

If you are moving from an inferior database :-) that implements unique keys via "identitity" then this is an easy means to keep the same functionality.

Thanks

Snehasish Das, October 08, 2015 - 1:41 pm UTC

Hi,

Thanks, I got your reference when you said Inferior database :D .

So as per my understanding , practically for most purposes we should use the standard sequencing methods.

Can you give One scenario where Identity column is helpful.


Regards,
Snehasish.
Connor McDonald
October 08, 2015 - 3:55 pm UTC

Identity is a convenience tool. It's less coding for you to set a column as identity compared to sequences.

Which approach you take is up to you.

A reader, April 03, 2017 - 6:40 pm UTC

When you said, the IDENTITY will create the sequence with default configuration if we don't specify anything and we can configure as based on our inputs... in that case what is the use of new feature sequence improvement in 12c itself.
Connor McDonald
April 04, 2017 - 1:12 am UTC

For me, the critical thing is the ability to do:

my_col default seq.nextval

That immediately removes the need for so many triggers out there in the application world.

Whether you implment that default as per above, or indirectly via an identity column probably comes down the preference/convenience.

But the ability to have the default is the cool part of the feature for me.

Identity columns in 12.2

Rajeshwaran, Jeyabal, July 30, 2017 - 5:48 am UTC

Team:

Was running through a demo at my test database in 12.2 and observed this behavior on identity columns.

prior to 12.2 dropping the table having an identity columns or dropping the identity configured to the column doesn't drop the implicit sequence created to support the identity columns.

but however that got changed with 12.2, either dropping the table or dropping the identity, now cleanup the implicit sequences.

here is my demo from 12.2

demo@ORA12C> create table transaction1(
  2     transaction_id int GENERATED ALWAYS AS IDENTITY,
  3     holding_id int,
  4     transaction_type number,
  5     transaction_date date,
  6     credit_value number,
  7     debit_value number );

Table created.

demo@ORA12C>
demo@ORA12C> create table transaction2(
  2     transaction_id int GENERATED BY DEFAULT AS IDENTITY,
  3     holding_id int,
  4     transaction_type number,
  5     transaction_date date,
  6     credit_value number,
  7     debit_value number );

Table created.

demo@ORA12C> select object_id from user_objects where object_name like 'TRANSACTION%';

 OBJECT_ID
----------
     87421
     87423

demo@ORA12C> select sequence_name from user_sequences;

SEQUENCE_NAME
----------------------------------------------------------------------------------------
ISEQ$$_87421
ISEQ$$_87423

demo@ORA12C> alter table transaction1
  2  modify transaction_id
  3  drop identity;

Table altered.

demo@ORA12C> drop table transaction2 purge;

Table dropped.

demo@ORA12C> select sequence_name from user_sequences;

no rows selected

demo@ORA12C>

Connor McDonald
July 31, 2017 - 2:53 am UTC

I didnt see an explicit bug reference, but I guess its part of a general cleanup in the area. For example,

Bug 18744247 - ORA-30667 ON DROPPING COLUMN WITH 'DEFAULT ON NULL' ATTRIBUTE
BUG:20330062 - SEQUENCE STILL CREATED WHEN ADDING IDENTITY COLUMN FAILS

were both fixed in 12.2
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.