Identity Column in Oracle 12c

Identity Column in Oracle 12c

Previous releases of the Oracle database, there was no substitute of the AutoNumber or Identity functionality like other database (SQL Server, MySQL etc). Same behaviour was implemented using a combination of sequences and triggers. However, Oracle 12c now introduces two alternatives 1) identity columns and 2) the ability to use sequence pseudocolumns as default values.

This article will focus on the use of identity columns. identity columns are also known as ‘Serrogate Keys’. These new columns have an ‘inherit sequence generator’ and will automatically generate their own column values. There are two methods of handling these keys:

1. Mandatory key values that cannot be overridden
2. Key values can be overridden

Identity Column Business Justification?

Why would you create Identity Column? There are many reasons why you would make a column Identity:

1. Keep particular column inherently unique.
2. Identity Column performance is better than Sequence and/or Trigger/Sequence combination
3. Requires less development i.e. no need for Trigger/Sequence; will result in the same data’s being loaded into table without your having to explicitly create a sequence

Identity Columns Behaviour

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. The syntax is shown in below screenshot.

ALWAYS:

Using ALWAYS forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced.

BY DEFAULT 

Using BY DEFAULT allows you to use the identity if the column isn’t referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. Attempting to specify the value NULL in this case results in an error, since identity columns are always NOT NULL

 

BY DEFAULT ON NULL 

Using BY DEFAULT ON NULL allows the identity to be used if the identity column is referenced, but a value of NULL is specified.

Based on the above screenshot, it is not difficult to deduce that a sequence is being used to populate the identity column

The ALL_TAB_IDENTITY_COLS views show information about identity columns

The link between the table and the sequence is stored in the SYS.IDNSEQ$ table.

 

Sequence usage is now visible in execution plans.

Performance Comparison

Not surprisingly, trigger-based test performs much worse than the others. The direct use of a sequence and the 12c identity column give comparable results, which are typically an order of magnitude faster than using a trigger to populate the ID column.

1. Creating Old Style Table/Trigger/Sequence

2. Comparison between New Identity Col VS using Trigger/Sequence

Not surprisingly, trigger-based test performs worse than the identity Column. The direct use of an identity column gives comparable results, which are typically an order of magnitude faster than using a trigger to populate the ID column.

Conclusion

We have explored Oracle 12c new feature of defining identity column. Following are the conclusion derived from the above:

• You can specify only one identity column per table.
• If you specify identity_clause, then you must specify a numeric data type for datatype in the column_definition clause. You cannot specify a user-defined data type.
• If you specify identity_clause, then you cannot specify the DEFAULT clause in the column_definition clause.
• When you specify identity_clause, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified. If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE, then an error is raised.
• If an identity column is encrypted, then the encryption algorithm may be inferred. Oracle recommends that you use a strong encryption algorithm on identity columns.
• CREATE TABLE AS SELECT will not inherit the identity property on a column

Source Oracle

About Author

Zafar Ali is the director of IDB Solutions LTD which provides BI solutions in UK. He has more than 10 years experience of Business Intelligence (BI) in two leading BI stacks (Oracle and Microsoft), cross platform data migration and integration. He always explores the possibilities of integrating cross platform BI technologies for clients to stitch heterogeneous systems to save cost, cut waste and improve efficiency.