Oracle 12c introduced the Identity Column. You can find out more about this feature in the new features guide and within the CREATE TABLE documentation.

In this article I will use Tom Kyte’s run stats utility to compare the performance of the IDENTITY column with the explicit use of a sequence object. The script below will be used to insert 10,0000, 100,000 and finally a million rows.

The tests were performed using Virtual Box running the pre built Database App Development VM. The database version at this time was 12.1.0.2.0 and all the examples were developed using SQL Developer 4.0.3.16

To support the tests,  the following objects are required. Firstly, a table using the IDENTITY column is created. Note the syntax for creating an IDENTITY column. In addition as I know there will be some large inserts into this table I have adjusted cache size of the sequence accordingly.

CREATE TABLE t_identity(id      NUMBER         GENERATED AS IDENTITY CACHE 1000
                                               CONSTRAINT t_identity_pk PRIMARY KEY,
                        details VARCHAR2(32))
/

Next a table and a sequence which will be used to hold the results of the inserts via a regular Oracle sequence is created. Again the sequence cache size has been increased from the default.

CREATE TABLE t_seq(id      NUMBER CONSTRAINT t_seq_pk PRIMARY KEY,
                   details VARCHAR2(32))
/

CREATE SEQUENCE s1 CACHE 1000
/

Below is the test script. As a brief overview, it initialises the call to the runstats package, it then inserts the required number of records into the table with the IDENTITY column.

The runstats package is called again to show that the first part of the processing has finished and the second part is about to start. The second insert is identical to the first one with the exception of the explicit call to the sequence object.

Thanks to Oracle Base for the tip about using TIMESTAMP as a seed to dbms_random. I am not advocating using row by row processing to insert volumes of data of this size in the real world!

BEGIN

   runstats_pkg.rs_start();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_identity(details)
      VALUES(l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_middle();

END;
/

DECLARE

   l_data VARCHAR2(32);
   l_seed VARCHAR2(32);

BEGIN

   l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');

   dbms_random.seed (val => l_seed);

   FOR i IN 1 .. 1000000
   LOOP

      l_data := dbms_random.string(opt => 'a', len => 32);

      INSERT INTO t_seq(id,
                        details)
      VALUES(s1.nextval,
             l_data);

   END LOOP;

   COMMIT;

END;
/

BEGIN

   runstats_pkg.rs_stop(1000);

END;
/

Here is the runstats output for each of the runs

10,000 rows inserted

Run1 ran in 106 cpu hsecs
Run2 ran in 105 cpu hsecs
run 1 ran in 100.95% of the time
...
Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff  Pct
171,097   171,432   335   99.80%

100,000 rows inserted

Run1 ran in 1216 cpu hsecs
Run2 ran in 1156 cpu hsecs
run 1 ran in 105.19% of the time
...
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff      Pct
1,719,582   2,061,024   341,442   83.43%

1,000,000 rows inserted

Run1 ran in 12308 cpu hsecs
Run2 ran in 11835 cpu hsecs
run 1 ran in 104% of the time
...
Run1 latches total versus runs -- difference and pct
Run1         Run2         Diff      Pct
18,480,661   18,761,711   281,050   98.50%

and the difference between the two methods is negligible.

Summary

In this article, using Tom Kyte’s runstats utility, I have invested the performance of the Oracle 12c new feature; IDENTITY column

Acknowledgements

Tom Kyte for his seminal package runstats