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:
Identity Column Business Justification?
Why would you create Identity Column? There are many reasons why you would make a column Identity:
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.
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.
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
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.
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.
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.
We have explored Oracle 12c new feature of defining identity column. Following are the conclusion derived from the above:
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.