Add columns and new features with default values in Oracle 11g

Before Oracle 11g, adding a column to a large table and setting the default value would be a tragedy. sometimes you have to select the online redefinition function to achieve this requirement. adding new columns and setting the default values in 11 GB only allows you to modify the data dictionary to achieve this function, greatly improving the efficiency.
Add 10g columns (default)

SQL> select * from v$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production SQL> create table t_xifenfei 2 as select object_id,object_name from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*)----------49827 SQL> desc t_xifenfeiName Null? Type----------------------------------------- -------- ----------------------------OBJECT_ID NUMBEROBJECT_NAME VARCHAR2(128) SQL> set timing onSQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null; Table altered. Elapsed: 00:00:06.13-- 6 seconds SQL> select rowid,2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,3 dbms_rowid.rowid_block_number(rowid)blockno,4 dbms_rowid.rowid_row_number(rowid) rowno5 from t_xifenfei where object_name='OBJ$'; ROWID REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAAMwJAAEAAAAB8AAr 4 124 43 SQL> alter system dump datafile 4 block 124; System altered. Elapsed: 00:00:00.08

11g add columns (default)

SQL> select * from v$version; BANNER-----------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production SQL> create table t_xifenfei 2 as select object_id,object_name from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*)----------74605 SQL> select rowid,2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,3 dbms_rowid.rowid_block_number(rowid)blockno,4 dbms_rowid.rowid_row_number(rowid) rowno5 from t_xifenfei where object_name='OBJ$'; ROWID REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAASpRAAEAAAACrAAu 4 171 46 SQL> alter system dump datafile 4 block 171; System altered.SQL> set timing onSQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null; Table altered. Elapsed: 00:00:00.19-- It only takes 0.19 seconds. SQL> select rowid,2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,3 dbms_rowid.rowid_block_number(rowid)blockno,4 dbms_rowid.rowid_row_number(rowid) rowno5 from t_xifenfei where object_name='OBJ$'; ROWID REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAASpRAAEAAAACrAAu 4 171 46 Elapsed: 00:00:00.04SQL> alter system dump datafile 4 block 171; System altered.

By adding the same columns and default values for 10g and 11g, we can find that the speed of 11g is much faster than that of 10g. Below we will analyze the cause by dumping the relevant data blocks above.
Dump Analysis
Dump before adding a column at 11g

tab 0, row 0, @0x1f74tl: 12 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 15col 1: [ 5] 49 43 4f 4c 24tab 0, row 1, @0x1f66tl: 14 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 2fcol 1: [ 7] 49 5f 55 53 45 52 31tab 0, row 2, @0x1f5btl: 11 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 1dcol 1: [ 4] 43 4f 4e 24

Dump after adding a column at 11g

tab 0, row 0, @0x1f74tl: 12 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 15col 1: [ 5] 49 43 4f 4c 24tab 0, row 1, @0x1f66tl: 14 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 2fcol 1: [ 7] 49 5f 55 53 45 52 31tab 0, row 2, @0x1f5btl: 11 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 1dcol 1: [ 4] 43 4f 4e 24tab 0, row 3, @0x1f4ftl: 12 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 10col 1: [ 5] 55 4e 44 4f 24

Dump after adding a column for 10 GB
Because the dump of 10g before adding a column is similar to that of 11g before adding a column, it is not dumped.

tab 0, row 0, @0x1f63tl: 29 fb: --H-FL-- lb: 0x2 cc: 3col 0: [ 2] c1 15col 1: [ 5] 49 43 4f 4c 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 1, @0x1f44tl: 31 fb: --H-FL-- lb: 0x2 cc: 3col 0: [ 2] c1 2dcol 1: [ 7] 49 5f 55 53 45 52 31col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 2, @0x1f28tl: 28 fb: --H-FL-- lb: 0x2 cc: 3col 0: [ 2] c1 1dcol 1: [ 4] 43 4f 4e 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 3, @0x1f0btl: 29 fb: --H-FL-- lb: 0x2 cc: 3col 0: [ 2] c1 10col 1: [ 5] 55 4e 44 4f 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

After adding a column at 11 GB, we found that the content dumped from the previous data has not changed. That is to say:In 10 Gb, we add a column and default value, which will be automatically added to the real data, adding columns and default values to 11G does not actually add existing data to the 11g table.
Insert new data in 11g dump Test

SQL> insert into chf.t_xifenfei(object_id,object_name)2 select object_id,object_name FROM DBA_OBJECTS; 74605 rows created. SQL> commit; Commit complete. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered. SQL> / System altered.-- Refresh SQL> SQL> SQL> select rowid,2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,3 dbms_rowid.rowid_block_number(rowid)blockno,4 dbms_rowid.rowid_row_number(rowid) rowno5 from chf.t_xifenfei where object_name='OBJ$'; ROWID REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAASpRAAEAAAACrAAu 4 171 46AAASpRAAEAAAB5TAAu 4 7763 46 SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 7763; System altered. -- Dump contenttab 0, row 0, @0x4e3tl: 29 fb: --H-FL-- lb: 0x1 cc: 3col 0: [ 2] c1 15col 1: [ 5] 49 43 4f 4c 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 1, @0x500tl: 31 fb: --H-FL-- lb: 0x1 cc: 3col 0: [ 2] c1 2fcol 1: [ 7] 49 5f 55 53 45 52 31col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 2, @0x51ftl: 28 fb: --H-FL-- lb: 0x1 cc: 3col 0: [ 2] c1 1dcol 1: [ 4] 43 4f 4e 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6dtab 0, row 3, @0x53btl: 29 fb: --H-FL-- lb: 0x1 cc: 3col 0: [ 2] c1 10col 1: [ 5] 55 4e 44 4f 24col 2: [16] 77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

Here we can see that:The data to be inserted later in 11 GB. The default value is also inserted into the data file.