DBA Hub

📋Steps in this guide1/3

DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)

This article describes the changes to table column defaults in Oracle Database 12c.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

DEFAULT Values Using Sequences

In Oracle 12c, it is now possible to specify the and sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose. In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the clause described in the next section. The fact we can use both the and pseudocolumns gives us the ability to auto-populate master-detail relationships, as shown below. Of course, this would only make sense if you could guarantee the inserts into the detail table would always immediately follow the insert into the master table, which would prevent you from using bulk-bind operations. A few things to remember about using sequence pseudocolumns as defaults include: - During table creation, the sequence must exist and you must have select privilege on it for it to be used as a column default. - The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table. - If the sequence is dropped after table creation, subsequent inserts will error. - Sequences used as default values are always stored in the data dictionary with fully qualified names. Normal name resolution rules are used to determine the sequence owner, including expansion of private and public synonyms. - As with any use of a sequence, gaps in the sequence of numbers can occur for a number of reasons. For example, if a sequence number is requested and not used, a statement including a sequence is rolled back, or the databases is turned off and cached sequence values are lost.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
CREATE SEQUENCE t1_seq;

CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

SELECT * FROM t1;

        ID DESCRIPTION
---------- ------------------------------
         1 DESCRIPTION only
       999 ID=999 and DESCRIPTION
           ID=NULL and DESCRIPTION

3 rows selected.

SQL>

CREATE SEQUENCE master_seq;
CREATE SEQUENCE detail_seq;

CREATE TABLE master (
  id          NUMBER DEFAULT master_seq.NEXTVAL,
  description VARCHAR2(30)
);

CREATE TABLE detail (
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)
);

INSERT INTO master (description) VALUES ('Master 1');
INSERT INTO detail (description) VALUES ('Detail 1');
INSERT INTO detail (description) VALUES ('Detail 2');

INSERT INTO master (description) VALUES ('Master 2');
INSERT INTO detail (description) VALUES ('Detail 3');
INSERT INTO detail (description) VALUES ('Detail 4');

SELECT * FROM master;

        ID DESCRIPTION
---------- ------------------------------
         1 Master 1
         2 Master 2

2 rows selected.

SQL>

SELECT * FROM detail;

        ID  MASTER_ID DESCRIPTION
---------- ---------- ------------------------------
         1          1 Detail 1
         2          1 Detail 2
         3          2 Detail 3
         4          2 Detail 4

4 rows selected.

SQL>
2

DEFAULT Values On Explicit NULLs

In the previous section we saw default values are only used when a column is not referenced in an insert statement. If the column is referenced, even when supplying the value NULL, the default value is not used. Oracle 12c allows you to modify this behaviour using the clause in the default definition. The following example compares the default action of the clause, with that of . The example uses sequences to populate two columns, one using the standard clause, the other using the clause. Notice the difference in the way the explicit NULL is handled. One other thing to consider is the implicitly sets the column to mandatory. In Oracle 23ai we can define a column as . This replaces explicit null values with the default value in update statements. - DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23ai

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE SEQUENCE default_seq;
CREATE SEQUENCE default_on_null_seq;

CREATE TABLE t2 (
  col1        NUMBER DEFAULT default_seq.NEXTVAL,
  col2        NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL,
  description VARCHAR2(30)
);

INSERT INTO t2 (description) VALUES ('DESCRIPTION only');
INSERT INTO t2 (col1, col2, description) VALUES (999, 999, '999,999,DESCRIPTION');
INSERT INTO t2 (col1, col2, description) VALUES (NULL, NULL, 'NULL,NULL,DESCRIPTION');

SELECT * FROM t2;

      COL1       COL2 DESCRIPTION
---------- ---------- ------------------------------
         1          1 DESCRIPTION only
       999        999 999,999,DESCRIPTION
                    2 NULL,NULL,DESCRIPTION

3 rows selected.

SQL>

desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                      NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(30)

SQL>
3

Metadata-Only DEFAULT Values

Prior to Oracle 11g, adding a new column to an existing table required all rows in that table to be modified to add the new column. Oracle 11g introduced the concept of metadata-only default values. Adding a column with a clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition. Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as or not. This represents both a space saving and performance improvement. There are some fairly obvious restrictions on this functionality, but it's worth checking the ALTER TABLE : DEFAULT section of the manual to make sure you are not hitting one. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!