Add column feature enhancements in Oracle 11g

1. Enhanced add column description
In Oracle 11gR1, Oracle enhanced the add column. Instructions on the official website:
Http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#NEWFTCH1
 
1.1 Enhanced add column Functionality
Default valuesof columns are maintained in the data dictionary for columns specifiedas not null.
-- If the column is not null, the default value of the column is maintained in the data dictionary.
 
Adding newcolumns with DEFAULT values and not null constraint nolonger requires the default value to be stored in all existing records. thisnot only enables a schema modification in sub-seconds and independent of theexisting data volume, it also consumes no space.
-- Add a column. This column is not empty and has a default value. It is not stored in all records in 11 GB. The default value is stored in the data dictionary separately, it is called from the data dictionary, which reduces the DDL operation time and space usage.
 
1.2 Adding Table Columns
To add a columnto an existing table, use the ALTERTABLE... ADD statement.
The followingstatement alters the hr. admin_emp table to add a new columnnamed bonus:
Alter table hr. admin_emp
ADD (bonus NUMBER (7,2 ));
 
If a new columnis added to a table, the column is initially NULL unless you specifythe DEFAULT clause. When you specify a default value, the databaseimmediately updates each row with the default value.
-- If a new column is added to the table, the column is initialized to NULL without specifying the default value. When the default value is specified, the data immediately updates all records in the table.
 
Note that thiscan take some time, and that during the update, there is an exclusive DML lockon the table. for some types of tables (for example, tables without LOBcolumns), if you specify both a not null constraint and adefault value, the database can optimize the column add operation and greatlyreduce the amount of time that the table is locked for DML.
-- Note that this update operation may take a lot of time, and an exclusive lock will be added to the table.
 
You can add acolumn with a not null constraint only if the table does notcontain any rows, or you specify a default value.
-- The not null restriction can be used only when there is no record in the table or the default value is specified.
 
1.3 Description
Through the above description, I have a certain understanding of add column. Before Oracle 11g, if we want to add a column that is not empty, we need to specify the default value. If the table is very large, after adding the column, update all records in the table and add new default values. This takes a long time and generates a large number of redo logs. Therefore, columns with default values must be added before the 11g period when the database is relatively idle.
 
The add column function is enhanced in Oracle 11g. In the above cases, all records in the table are not updated in 11g, but the default value is saved to the data dictionary. When you query records of this column, you can obtain the default value from the data dictionary (sys. col $. default $. This can reduce the system overhead.
 
Note:
When the first column is added, the ecol $ and col $ dictionaries are updated at the same time. However, if you modify this default value later, only the values in col $ are modified, our future queries are also obtained from col $, while in ecol $, the default value we first assigned is always saved.
 
Description of a BUG related to this function on MOS:
Wrong Result For Added Column After TableCreation in 11g [ID 1106553.1]
 
Ii. Example
 
2.1 add column operation example
SQL> select * from v $ version;
 
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
 
SQL> create table t1 (id number, namevarchar2 (20 ));
Table created.
SQL> insert into t1 values (1, 'Dave ');
1 row created.
SQL> insert into t1 values (2, 'anqing ');
1 row created.
SQL> insert into t1values (3, 'inserting ');
1 row created.
 
SQL> commit;
Commit complete.
 
SQL> select * from t1;
ID NAME
---------------------------
1 dave
2 anqing
3 Training
 
Before executing add column, we enable the 10046 event to track this process:
 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 8;
Statement processed.
 
-- Execute the operation
SQL> alter table t1 add tel varchar2 (20) default '20140901' not null;
Table altered.
 
SQL> select * from t1;
 
ID NAME TEL
--------------------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 Training 13888888888
 
-- Close the 10046 event and view the trace:
SQL> oradebug event 10046 trace namecontext off;
Statement processed.
SQL> oradebug tracefile_name
D: \ app \ administrator \ diag \ rdbms \ newccs \ trace \ newccs_ora_308.trc
 
For more information about the 10046 event, refer to my Blog:
Oracle SQLTrace and 10046 events
Http://blog.csdn.net/tianlesoftware/article/details/5857023
 
2.2 analyze the trace file
 
View the content in the newccs_ora_308.trc file and search for it:
==================================
Parsing in cursor #11 len = 445 dep = 1 uid = 0oct = 6 lid = 0 tim = 5734874878 hv = 1706555580 ad = 'b61eda64' sqlid = 'dbcjpknkvgy5w'
Update col $ setname =: 3, segcol # =: 4, type # =: 5, length =: 6, precision # = decode (: 5,182/* DTYIYM */,: 7,183/* DTYIDS */,: 7, decode (:, null,: 7), scale = decode (: 5, 2, decode (: 8, -127/* MAXSB1MINAL */, null,: 8), 178,: 8,179,: 8,180,: 8,181,: 8,182,: 8,183,: 8,231,: 8, null ), null $ =: 9, fixedstorage =: 10, segcollength =: 11, col # =: 12, property =: 13, charsetid =: 14, charsetform =: 15, spare1 =: 16, spare2 =: 17, spare3 =: 18, deflength = decode (: 19,0, null,: 19), default $ =: 20 where obj # =: 1 and intcol # =: 2
END OF STMT
PARSE #11: c = 0, e = 857, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 0, tim = 5734874874
EXEC #11: c = 15600, e = 23270, p = 0, cr = 2, cu = 7, mis = 1, r = 1, dep = 1, og = 4, plh = 511615611, tim = 5734898508
STAT #11 id = 1 cnt = 0 pid = 0pos = 1 obj = 0 op = 'Update COL $ (cr = 2 pr = 0pw = 0 time = 0 us )'
STAT #11 id = 2 cnt = 1 pid = 1 pos = 1 obj = 50op = 'index unique scan I _COL3 (cr = 2 pr = 0 pw = 0 time = 0 us cost = 1 size = 59 card = 1)'
CLOSE #11: c = 0, e = 4, dep = 1, type = 3, tim = 5734898829
==================================
Parsing in cursor #4 len = 37 dep = 1 uid = 0oct = 2 lid = 0 tim = 5734910715 hv = 4050124187 ad = 'b61ed628 'sqlid = 'cqrnq6vsqgzcv'
Insert into ecol $ values (: 1,: 2,: 3)
END OF STMT
PARSE #4: c = 0, e = 578, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 0, tim = 5734910712
EXEC #4: c = 0, e = 973, p = 0, cr = 2, cu = 3, mis = 1, r = 1, dep = 1, og = 4, plh = 0, tim = 5734912051
STAT #4 id = 1 cnt = 0 pid = 0 pos = 1 obj = 0op = 'Load table conventional (cr = 2 pr = 0pw = 0 time = 0 us )'
CLOSE #4: c = 0, e = 4, dep = 1, type = 3, tim = 5734912234
==================================
Parsing in cursor #10 len = 97 dep = 1 uid = 0oct = 3 lid = 0 tim = 5734913014 hv = 2759248297 ad = 'b61ed1ac' sqlid = 'aa35g82k7dkd9'
Select binaryDefVal, length (binaryDefVal) from ecol $ where tabobj # =: 1 and colnum =: 2
END OF STMT
PARSE #10: c = 0, e = 596, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 0, tim = 5734913010
EXEC #10: c = 0, e = 1149, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 3081038021, tim = 5734914364
FETCH #10: c = 0, e = 65, p = 0, cr = 2, cu = 0, mis = 0, r = 1, dep = 1, og = 4, plh = 3081038021, tim = 5734914511
STAT #10 id = 1 cnt = 1 pid = 0 pos = 1 obj = 123op = 'table access by index rowid ecol $ (cr = 2 pr = 0 pw = 0 time = 0 us cost = 1 size = 2028 card = 1)'
STAT #10 id = 2 cnt = 1 pid = 1 pos = 1 obj = 126op = 'index range scan ECOL_IX1 (cr = 1 pr = 0 pw = 0 time = 0 us cost = 1 size = 0 card = 1)'
CLOSE #10: c = 0, e = 4, dep = 1, type = 3, tim = 5734914692
 
The format is messy. Use tkprof to format it:
C: \ Users \ Administrator. David Dai> tkprof d: \ app \ administrator \ diag \ rdbms \ newccs \ trace \ newccs_ora_308.trc d: \ dave.txt
 
TKPROF: Release 11.2.0.1.0-Development onWed Feb 1 22:09:55 2012
 
Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All rights reserved.
 
Search Results:
SQL ID: 60uw2vh6q9vn2
Plan Hash: 0
Insert into Col $ (obj #, name, intcol #, segcol #, type #, length, precision #, scale,
Null $, offset, fixedstorage, segcollength, deflength, default $, col #, property,
Charsetid, charsetform, spare1, spare2, spare3)
Values
(: 1,: 2,: 3,: 4,: 5,: 6, decode (: 5,182/* DTYIYM */,: 7,183/* DTYIDS */,: 7, decode (: 7,0,
Null,: 7), decode (: 5, 2, decode (: 8,-127/* MAXSB1MINAL */, null,: 8), 178,: 8,179,: 8,
180,: 8,181,: 8,182,: 8,183,: 8,231,: 8, null),: 9, 0,: 10,: 11, decode (: 12, 0, null,: 12)
,: 13,: 14,: 15,: 16,: 17,: 18,: 19,: 20)
 
SQL ID: dbcjnkpkvgy5w
Plan Hash: 511615611
Update col $ setname =: 3, segcol # =: 4, type # =: 5, length =: 6, precision # = decode (: 5,
182/* DTYIYM */,: 7,183/* DTYIDS */,: 7, decode (:, null,: 7), scale = decode (: 5, 2,
Decode (: 8,-127/* MAXSB1MINAL */, null,: 8), 178,: 8,179,: 8,180,: 8,181,: 8,182,
183,: 8,231,: 8, null), null $ =: 9, fixedstorage =: 10, segcollength =: 11, col # =: 12,
Property =: 13, charsetid =: 14, charsetform =: 15, spare1 =: 16, spare2 =: 17, spare3 =: 18,
Deflength = decode (: 19,0, null,: 19), default $ =: 20
Where obj # =: 1 and intcol # =: 2
 
SQL ID: cqrnq6vsqgzcv
Plan Hash: 0
Insert into ecol $ values (: 1,: 2,: 3)
 
SQL ID: aa35g82k7dkd9
Plan Hash: 3081038021
Select binaryDefVal, length (binaryDefVal)
From ecol $ where tabobj # =: 1 and colnum =: 2
-- Note that both ecol $ and col $ are updated here.
 
2.3 differences between test ecol $ and col $:
 
View sys. ecol $
SQL> select * from sys. ecol $;
 
TABOBJ # COLNUM BINARYDEFVAL
-------------------------------------------------
83210 3 3133383838383838383838
 
SQL> desc ecol $;
Name Null? Type
--------------------------------------------------------
TABOBJ # NUMBER
COLNUM NUMBER
BINARYDEFVAL BLOB
 
Note that the final value here is BLOB.
 
You can use the following SQL statement to view the corresponding values:
SQL> select utl_raw.cast_to_varchar2 (dbms_lob.substr (BINARYDEFVAL) AS TEL from sys. ecol $;
TEL
--------------------------------------------------------------------------------
13888888888
 
Here we return our previous settings. For more information about LOB, refer to my Blog:
Oracle lob large object processing http://www.bkjia.com/database/201202/118146.html
 
Using sys. col $ is simple:
SQL> select obj #, name, default $ fromsys. col $ where obj #= 83210;
 
OBJ # name default $
--------------------------------------------------------------
83210 ID
83210 NAME
83210 TEL '000000'
 
Now let's modify the default value of this field:
SQL> alter table t1 modify tel default '20140901 ';
Table altered.
 
SQL> select * from t1;
 
ID NAME TEL
--------------------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 Training 13888888888
 
SQL> insert into t1 (id, name) values (4, 'hefei ');
1 row created.
 
SQL> select * from t1;
 
ID NAME TEL
--------------------------------------------------
1 dave 13888888888
2 anqing 13888888888
3 Training 13888888888
4 hefei 13899999999
 
SQL> commit;
Commit complete.
 
View the corresponding records in ecol $ and col $ again:
SQL> select obj #, name, default $ fromsys. col $ where obj #= 83210;
 
OBJ # name default $
--------------------------------------------------------------
83210 ID
83210 NAME
83210 TEL '000000'
-- Col $ becomes 13899999999
 
SQL> selectutl_raw.cast_to_varchar2 (dbms_lob.substr (BINARYDEFVAL) AS TEL from sys. ecol $;
TEL
--------------------------------------------------------------------------------
13888888888
-- Note that the value in ecol $ has not changed.
 
This verifies our previous instructions. The default value assigned by the first option will be permanently saved in ecol $. If we modify this default value, then only the value in col $ will be modified, we also retrieve data from this data dictionary when querying.
 
 
 
 
 
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!
Email: tianlesoftware@gmail.com
Skype: tianlesoftware
Blog: http://www.tianlesoftware.com
WEAVER: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware