Oracle 11g adds not null fields faster than 10g-new feature, oracle11g

Oracle 11g adds not null fields faster than 10g-new feature, oracle11g

Adding a not null field before 11g is very slow, and it is very fast after 11g. We will first perform a test and then explore the principle.

SQL> select * from v $ version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production

SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> select count (*) from test;
COUNT (*)
----------
151203
SQL> set timing on
SQL> alter table test add col1 char (1000) DEFAULT 'large column' not null;
Used time: 00: 00: 09.48

SQL> SELECT SUM (BYTES)/1024/1024 | 'M' FROM user_segments WHERE segment_name = 'test ';
SUM (BYTES)/1024/1024 | 'M'
-----------------------------------------
200 M

Under 11g:
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> insert into test select * from dba_objects;
SQL> commit;
SQL> select count (*) from test;
COUNT (*)
----------
149012
SQL> set timing on
SQL> alter table test add col1 char (1000) DEFAULT 'large column' not null;

Used time: 00: 00: 00.07

SQL> SELECT SUM (BYTES)/1024/1024 | 'M' FROM user_segments WHERE segment_name = 'test ';
SUM (BYTES)/1024/1024 | 'M'
-----------------------------------------

18 M

Exploring principle: dump block can be found that many row migration and row links are generated at 10 Gb. In the official document, Oracle records the DEFAULT value in the data dictionary to avoid heavy update operations.

Look at the dump block:
Alter session set tracefile_identifier = 'gg _ test ';
Select rowid,
Dbms_rowid.rowid_object (rowid) object_id,
Dbms_rowid.rowid_relative_fno (rowid) file_id,
Dbms_rowid.rowid_block_number (rowid) block_id,
Dbms_rowid.rowid_row_number (rowid) num
From test where rownum <5;
Alter system dump datafile 5 block 1465683;

10 GB:
Tl: 9 fb: -- H ----- lb: 0x2 cc: 0
Nrid: 0x0181e36b. 1
Tab 0, row 2, @ 0x1b3a
Tl: 1076 fb: -- H-FL -- lb: 0x2 cc: 14
Col 0: [3] 53 59 53
Col 1: [4] 43 4f 4e 24
Col 2: * NULL *
Col 3: [2] c1 1d
Col 4: [2] c1 1d
Col 5: [5] 54 41 42 4c 45
Col 6: [7] 78 69 08 1e 0e 33 19
Col 7: [7] 78 69 08 1e 0f 31 37
Col 8: [19] 32 30 30 35 2d 30 38 2d 33 30 3a 31 33 3a 35 30 3a 32 34
Col 9: [5] 56 41 4c 49 44
Col 10: [1] 4e
Col 11: [1] 4e
Col 12: [1] 4e
Col 13: [2, 1000]
4c 41 52 47 45 20 43 4f 4c 55 4d 4e 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
Tab 0, row 3, @ 0x1b31
Tl: 9 fb: -- H ----- lb: 0x2 cc: 0
Nrid: 0x0181e36b. 2 -- nrid row migration
Tab 0, row 4, @ 0x1b28
Tl: 9 fb: -- H ----- lb: 0x2 cc: 0
Nrid: 0x0181e36b. 3

Under 11g:
Tab 0, row 0, @ 0x3f33
Tl: 77 fb: -- H-FL -- lb: 0x0 cc: 14
Col 0: [3] 53 59 53
Col 1: [5] 49 43 4f 4c 24
Col 2: * NULL *
Col 3: [2] c1 15
Col 4: [2] c1 03
Col 5: [5] 54 41 42 4c 45
Col 6: [7] 78 72 06 09 0c 1a 2f
Col 7: [7] 78 72 06 09 0c 26 33
Col 8: [19] 32 30 31 34 2d 30 36 2d 30 39 3a 31 31 3a 32 35 3a 34 36
Col 9: [5] 56 41 4c 49 44
Col 10: [1] 4e
Col 11: [1] 4e
Col 12: [1] 4e
Col 13: [2] c1 02
Tab 0, row 1, @ 0x3ee4
Tl: 79 fb: -- H-FL -- lb: 0x0 cc: 14
Col 0: [3] 53 59 53
Col 1: [7] 49 5f 55 53 45 52 31
Col 2: * NULL *
Col 3: [2] c1 2f
Col 4: [2] c1 2f
Col 5: [5] 49 4e 44 45 58
Col 6: [7] 78 72 06 09 0c 1a 2f
Col 7: [7] 78 72 06 09 0c 1a 2f
Col 8: [19] 32 30 31 34 2d 30 36 2d 30 39 3a 31 31 3a 32 35 3a 34 36
Col 9: [5] 56 41 4c 49 44
Col 10: [1] 4e
Col 11: [1] 4e
Col 12: [1] 4e
Col 13: [2] c1 05

Add not null to the date field of oracle

Alter table modify a not null;
This is required.
However, if there is a null value in this column, you must delete the null value before modification.

Note the following operations on columns in oracle:
1. Add Columns

Alter table table_name ADD (column datatype [default expr] [, column datatype...]);

For example:

SQL> ALTER TABLE emp01 ADD eno NUMBER (4 );

2. Modify column Definitions

For example:

SQL> ALTER TABLE emp01 MODIFY job VARCHAR2 (15)

2 DEFAULT 'cler'

3. Delete Columns

For example:

SQL> ALTER TABLE emp01 DROP COLUMN dno;

4. Modify the column name

For example:

SQL> ALTER TABLE emp01 RENAME COLUMN eno TO empno;

5. Modify the table name

For example:

SQL> RENAME emp01 TO employee;

6. Add comments

For example:

SQL> COMMENT ON TABLE employee IS 'employee information ';

SQL> COMMENT ON TABLE employee. name IS 'employee name ';

Oracle changes column attributes: changes the previously set null of a column to not null. The following is my operation: an error is returned.

Alter table tablename modify (column datatype [default value] [null/not null],...);
So we should add the current field type