New features that add columns and default values to Oracle11g

Before Oracle11g, if you want to add a column in a large table and set the default value, it will be a very tragic thing. Sometimes you have to choose the online redefinition function to implement it.

Before Oracle 11g, if you want to add a column in a large table and set the default value, it will be a very tragic thing. Sometimes you have to choose the online redefinition function to implement it.

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

[Date:] Source: Linux community Author: Linux [Font:]

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-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0-Production

NLSRTL 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_xifenfei

Name Null? Type

-----------------------------------------------------------------------------

OBJECT_ID NUMBER

OBJECT_NAME VARCHAR2 (128)

SQL> set timing on

SQL> 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) rowno

5 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-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL 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) rowno

5 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 on

SQL> 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) rowno

5 from t_xifenfei where object_name = 'obj $ ';

ROWID REL_FNO BLOCKNO ROWNO

------------------------------------------------

AAASpRAAEAAAACrAAu 4 171 46

Elapsed: 00:00:00. 04

SQL> 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, @ 0x1f74

Tl: 12 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 15

Col 1: [5] 49 43 4f 4c 24

Tab 0, row 1, @ 0x1f66

Tl: 14 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 2f

Col 1: [7] 49 5f 55 53 45 52 31

Tab 0, row 2, @ 0x1f5b

Tl: 11 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 1d

Col 1: [4] 43 4f 4e 24

Dump after adding a column at 11g

Tab 0, row 0, @ 0x1f74

Tl: 12 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 15

Col 1: [5] 49 43 4f 4c 24

Tab 0, row 1, @ 0x1f66

Tl: 14 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 2f

Col 1: [7] 49 5f 55 53 45 52 31

Tab 0, row 2, @ 0x1f5b

Tl: 11 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 1d

Col 1: [4] 43 4f 4e 24

Tab 0, row 3, @ 0x1f4f

Tl: 12 fb: -- H-FL -- lb: 0x0 cc: 2

Col 0: [2] c1 10

Col 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, @ 0x1f63

Tl: 29 fb: -- H-FL -- lb: 0x2 cc: 3

Col 0: [2] c1 15

Col 1: [5] 49 43 4f 4c 24

Col 2: [16] 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

Tab 0, row 1, @ 0x1f44

Tl: 31 fb: -- H-FL -- lb: 0x2 cc: 3

Col 0: [2] c1 2d

Col 1: [7] 49 5f 55 53 45 52 31

Col 2: [16] 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

Tab 0, row 2, @ 0x1f28

Tl: 28 fb: -- H-FL -- lb: 0x2 cc: 3

Col 0: [2] c1 1d

Col 1: [4] 43 4f 4e 24

Col 2: [16] 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

Tab 0, row 3, @ 0x1f0b

Tl: 29 fb: -- H-FL -- lb: 0x2 cc: 3

Col 0: [2] c1 10

Col 1: [5] 55 4e 44 4f 24

Col 2: [16] 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d