This blog posting is about another new Feature in Oracle 12.1: The new release allows to populate
(Primary Key) columns automatically. In the past, we needed to create explicit sequence and trigger
objects for this, as follows ...

Starting with Oracle11g, it was possible to replace the select ... into statement
by a simple PL/SQL assignment within the trigger. This cleans up the code, but (as we'll see)
it will not run much faster.

The Identity Column Feature goes a step further: An explicit sequence is no longer
needed - we just declare the "identity column". If needed, the sequence details can be directly added to
the column definition.

The syntax itself does not change: The definition, which was previously part of the
CREATE SEQUENCE command, is now part of CREATE TABLE. The data dictionary contains
a new view with information about identity columns.

From a technical point of view, an Identity Column is a Sequence By Default. It allows
to simplify DDL scripts by having the sequence details in the CREATE TABLE statement.
Developers does not have to care about sequence objects any more: The database will maintain them
automatically - which includes dropping them after the table has been dropped (try it). Regaring performance,
both variants should behave similar - and finally we'll do a (very) simple performance test by
inserting 10.000 values into the table. The
exact numbers will depend on the system, but the indication we'll get, should apply
in general.

In this example, the performance of the trigger variants is roughly equal. Some times
the trigger with the PL/SQL assignment was faster, other times, the SELECT .. INTO was - there
was no clear picture. But Identity Columns or columns having Sequence as Default are significantly
faster - we can clearly see the cost of calling the Trigger and having the context switch from
SQL to PL/SQL.

Summarized, I think that this new feature will be adopted rather quickly - DDL scripts can be
much cleaner with Oracle12c. Those who still want to control the "number generator", can use
Sequence By Default and create their sequence objects manually. The others might use IdentityColumns, handing sequence maintenance completely over to the database.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.