Oracle column self-increment implementation (2)-identity Columns in Oracle Database 12c Release 1 (12.1)

Oracle column self-increment-identity Columns in Oracle Database 12c Release 1 (12.1)

In previous versions of Oracle 12C, if the column was to grow from a sequence + trigger, the new Identity columns feature was introduced into the 12C Oracle, which enabled the column self-growth function to be realized.

I. Identity columns using syntax

GENERATED []  ]asIDENTITY[] identity_ Options

Second, Identity_clause

2.1 Always option

DROP TABLE Identity_test_tab PURGE; CREATE TABLE  number as IDENTITY VARCHAR2 (());

Insert Test 1:

INSERT  into VALUES ('Just DESCRIPTION')

[SQL] INSERT into Identity_test_tab (description) VALUES (' Just description ')

Rows affected: 1

Time: 0.008s

Insert Test 2:

INSERT  into VALUES (NULL'id=null and DESCRIPTION')

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (NULL, ' id=null and description ')

[ERR] Ora-32795:cannot insert into a generated constant identity column cannot be inserted into the Always build identity

Insert Test 3:

INSERT  into VALUES (999'id=999 and DESCRIPTION')

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (999, ' id=999 and description ')

[ERR] Ora-32795:cannot INSERT into a generated always identity column

Update test:

UPDATE SET ID=2WHERE id=1

[SQL] UPDATE identity_test_tab SET id=2 WHERE id=1

[ERR] Ora-32796:cannot update a generated always identity column

Conclusion:

1. GENERATED always as IDENTITY can be inserted without specifying the column
2. GENERATED always as identity cannot insert a null value in this column
3. GENERATED always as identity cannot specify a specific value to insert
4. GENERATED always as IDENTITY cannot update the column using update

2.2 BY DEFAULT 选项

DROP TABLE Identity_test_tab PURGE; CREATE TABLE  number byDEFAULTasIDENTITYVARCHAR2 ());

Insert Test 1:

INSERT  into VALUES ('Just DESCRIPTION');

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (999, ' id=999 and description ')

Rows affected: 1

Time: 0.001s

SELECT *  from Identity_test_tab;

Insert Test 2:

INSERT  into VALUES (999'id=999 and DESCRIPTION');

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (999, ' id=999 and description ')

Rows affected: 1

Time: 0.001s

SELECT *  from Identity_test_tab;

Insert Test 3:

INSERT  into VALUES (NULL'id=null and DESCRIPTION');

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (NULL, ' id=null and description ')

[ERR] Ora-01400:cannot insert NULL into ("Test_user". " Identity_test_tab "." ID ")

Update test:

UPDATE SET ID=2WHERE id=1

[SQL] UPDATE identity_test_tab SET id=2 WHERE id=1

Rows affected: 1

Time: 0.001sUPDATE identity_test_tab SET id=2 WHERE id=1

Conclusion:

1. GENERATED by DEFAULT as IDENTITY can be inserted without specifying the column
2. GENERATED by default as IDENTITY can specify a specific value to insert
3. GENERATED by DEFAULT as IDENTITY cannot insert a null value in this column
4. The column can be updated with update, but cannot be updated to null

2.3 DEFAULT ON NULL 选项

DROP TABLE Identity_test_tab PURGE; CREATE TABLE  number byDEFAULTonNULLasIDENTITY  VARCHAR2(());

Insert test:

INSERT  intoIdentity_test_tab (description)VALUES('Just DESCRIPTION');INSERT  intoIdentity_test_tab (ID, description)VALUES(999,'id=999 and DESCRIPTION');INSERT  intoIdentity_test_tab (ID, description)VALUES(NULL,'Id=null and DESCRIPTION');

[SQL] INSERT into Identity_test_tab (description) VALUES (' Just description ')

Rows affected: 1

Time: 0.003s

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (999, ' id=999 and description ')

Rows affected: 1

Time: 0.001s

[SQL] INSERT into Identity_test_tab (ID, description) VALUES (NULL, ' id=null and description ')

Rows affected: 1

Time: 0.002s

SELECT *  from Identity_test_tab;

Update test

UPDATE SET ID=3WHERE id=1

[SQL] UPDATE identity_test_tab SET id=3 WHERE id=1

Rows affected: 1

Time: 0.004s

Conclusion:

1. GENERATED by DEFAULT on NULL as IDENTITY can be inserted without specifying the column
2. GENERATED by DEFAULT on NULL as IDENTITY means you can specify a specific value to insert
3. GENERATED by DEFAULT on NULL as IDENTITY can insert a null value in this column
4. You can update the column with update

Third, the principle 3.1 Identity Columns is based on sequence implementation

Using this syntax to implement ID self-increment requires that you have permission to create a sequence. It can be inferred that it is based on sequence implementation

After you finish executing the Build Table statement:

CREATE TABLE  number as IDENTITY VARCHAR2 (());

View User_objects

SELECT object_name  from User_objects;

A sequence is automatically generated when the table is found.

SELECT table_name, Column_name,generation_type,identity_options  from All_tab_identity_cols WHERE = ' Test_user ';

The relationship between table and sequence existsSYS.IDNSEQ$表中

Sys登陆查看

SELECT  as  as   sequence_name from sys.idnseq$ cJOINon= a.obj# JOIN  on = b.obj# where a.name='identity_test_tab';

Sequence cannot be deleted individually in 3.2 GENERATED IDENTITY

DROP TABLE Identity_test_tab

After you delete the table, the sequence still exists. And the sequence cannot be deleted

Have to

Table Identity_test_tab,

Conclusion:

1. The Identity Columns is based on the sequence implementation
2. Sequence cannot be deleted individually in GENERATED IDENTITY
3. GENERATED the table in the IDENTITY is deleted, if there is a recycle Bin, the sequence is still stored, and if the table is completely deleted, sequence is also deleted

3.3 Interpretation Plan when executing an INSERT statement

INSERT into Identity_test_tab (description) VALUES (' Just description ');

Comparison found: This mode of efficiency than the trigger + sequence of the way high!

Iv. identity_options

To view the DDL for a table

 select dbms_metadata.get_ddl ( table ",  identity_ Test_tabfrom DUAL;      

CREATE TABLE "Test_user". " Identity_test_tab "

("ID" number GENERATED always as IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT by 1 START with 1 CA CHE Noorder nocycle not NULL ENABLE,

"DESCRIPTION" VARCHAR2 (30)

) SEGMENT CREATION IMMEDIATE

PCTFREE pctused Initrans 1 Maxtrans 255

Nocompress LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 MAXEXTENTS 2147483645

Pctincrease 0 freelists 1 FREELIST GROUPS 1

Buffer_pool default Flash_cache default Cell_flash_cache default)

Tablespace "USERS"

INCREMENT by

Used to define the step of the sequence, if omitted, the default is 1, and if a negative value is present, the values of the sequence are decremented by this step.

START with

Defines the initial value of the Oracle sequence (that is, the first value produced), which defaults to 1.

MAXVALUE

Defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum definition, at which point the system can produce a maximum value of 10 for the increment sequence, and the maximum value for the descending sequence is-1.

MINVALUE

Defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no minimum value definition,

CYCLE and Nocycle

Indicates whether to loop after the value of the sequence generator reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

CACHE

(buffered) Defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a sequence can improve the performance of the sequence.

Original address:

Oracle 12C new feature identity columns-enables Oracle self-growing columns

Identity Columns in Oracle Database 12c Release 1 (12.1)

Database SQL Language Reference

Oracle column self-increment implementation (2)-identity Columns in Oracle Database 12c Release 1 (12.1)