| How works add column to table [message #622876] |
Mon, 01 September 2014 04:48  |
tazcox
Messages: 1
Registered: August 2010
Location: switzerland
|
Junior Member |
|
|
hello
i have a question ..:
friday a developer was juste add columns to a very big table, and the database was generated many archivelogs until 100% FS
anyone could explain me how works oracle and what it do when add columns to big table ? i just want understand why it was generated many archivelog , and if is possible to not do the archivelog with any option (no log or something like that )
Thanks for your explaination ..
|
|
|
|
|
|
|
|
| Re: How works add column to table [message #622879 is a reply to message #622877] |
Mon, 01 September 2014 05:44  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
John Watson wrote on Mon, 01 September 2014 10:54Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Adding columns generates minimal redo, unless they have a default value. In that case, the redo will be signficant.
The column addition is a simple data dictionary change, but populating the column is a DML operation that will generate undo and redo. furthermore, it may well result in large scale row migration, which will add to the problem.
Dont most recent versions have fast default (if using not null at the same time) - so it is still a dictionary operation and only the subsequent selects generate the redo?
No version given in OP right enough...
|
|
|
|
| Re: How works add column to table [message #622882 is a reply to message #622879] |
Mon, 01 September 2014 06:38 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I haven't read about "fast default", it must be a new feature I missed. Thank you for pointing it out. But this simple test (using 12.1.0.1) doesn't show ANY significant redo begin generated, at the time of the DDL or subsequent SELECT:
orclz>
orclz> create table t1(c1 number);
Table created.
orclz> insert into t1 select 1 from dual connect by level < 100000;
99999 rows created.
orclz> select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
'REDO:'||VALUE
----------------------------------------------
redo: 1428728
orclz> select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
'UNDO:'||VALUE
----------------------------------------------
undo: 243560
orclz> alter table t1 add (c2 varchar2(10) default '1234567890');
Table altered.
orclz> select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
'REDO:'||VALUE
----------------------------------------------
redo: 1440532
orclz> select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
'UNDO:'||VALUE
----------------------------------------------
undo: 247396
orclz> select count(c2) t1;
select count(c2) t1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
orclz> select count(c2) from t1;
COUNT(C2)
----------
99999
orclz> select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
'REDO:'||VALUE
----------------------------------------------
redo: 1440532
orclz> select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
'UNDO:'||VALUE
----------------------------------------------
undo: 247396
orclz> set autot trace stat
orclz> select * from t1;
99999 rows selected.
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
6868 consistent gets
0 physical reads
0 redo size
1773867 bytes sent via SQL*Net to client
73870 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed
orclz>
|
|
|
|
|
|
| Re: How works add column to table [message #622888 is a reply to message #622882] |
Mon, 01 September 2014 06:56 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Fast works when you add a column with default and NOT NULL.
Without NOT NULL:
SQL> @v
Version Oracle : 11.2.0.3.0
SQL> set timing on
SQL> drop table t1;
Table dropped.
Elapsed: 00:00:00.93
SQL> create table t1(c1 number);
Table created.
Elapsed: 00:00:00.03
SQL> insert into t1 select 1 from dual connect by level < 100000;
99999 rows created.
Elapsed: 00:00:00.14
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('redo size','undo change vector size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1432800
undo change vector size 247408
2 rows selected.
Elapsed: 00:00:00.02
SQL> alter table t1 add (c2 varchar2(10) default '1234567890');
Table altered.
Elapsed: 00:00:11.90
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('redo size','undo change vector size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 79321188
undo change vector size 23953316
2 rows selected.
Elapsed: 00:00:00.02
With NOT NULL:
SQL> drop table t1;
Table dropped.
Elapsed: 00:00:00.49
SQL> create table t1(c1 number);
Table created.
Elapsed: 00:00:00.02
SQL> insert into t1 select 1 from dual connect by level < 100000;
99999 rows created.
Elapsed: 00:00:00.26
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('redo size','undo change vector size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 80753920
undo change vector size 24200640
2 rows selected.
Elapsed: 00:00:00.00
SQL> alter table t1 add (c2 varchar2(10) default '1234567890' not null);
Table altered.
Elapsed: 00:00:00.56
SQL> select name, value from v$mystat natural join v$statname
2 where name in ('redo size','undo change vector size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 80770584
undo change vector size 24205632
2 rows selected.
Elapsed: 00:00:00.02
|
|
|
|
| Re: How works add column to table [message #622894 is a reply to message #622888] |
Mon, 01 September 2014 07:51 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 01 September 2014 07:56
Fast works when you add a column with default and NOT NULL.
Just to clarify, it works fast starting 11g (so OP needs to provide version) where adding NOT NULL column with DEFAULT simply results in Oracle modifying just metadata - data dictionary. However there is a penalty. When Oracle retrieves data for NOT NULL column with DEFAULT it has to do an extra check to see if column stored value is NULL and then return DEFAULT value instead. Also, we need to keep in mind this fast solution is based on column being NOT NULL. Oracle will be forced to update column to default value in every row where column stored value is NULL if later we decide to change column to NULL:
SQL> create table tbl
2 as
3 select level x
4 from dual
5 connect by level <= 1000000
6 /
Table created.
SQL> set timing on
SQL> alter table tbl
2 add y varchar2(10) default 'XXXXXXXXXX' not null
3 /
Table altered.
Elapsed: 00:00:00.02
SQL> alter table tbl
2 modify y null
3 /
Table altered.
Elapsed: 00:02:00.59
SQL> set timing off
SQL>
SY.
[Updated on: Mon, 01 September 2014 07:56] Report message to a moderator |
|
|
|
| Re: How works add column to table [message #622896 is a reply to message #622894] |
Mon, 01 September 2014 08:37 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Yes, the reason is simple: Oracle has to know the meaning of the NULL.
When you add a column with NOT NULL (and DEFAULT), it then knows that the NULL value in this column is in fact a default value as no new row can be inserted with a NULL in this place.
If you add a column with NULL (and DEFAULT) then, after a while, Oracle couldn't know if the NULL is an original one (when the column was added) or due to new DML; so it has to first update all rows with the default value.
The situation is the same one when you change this NOT NULL to NULL. As Oracle will not know, after this statement, if the NULL will be due to new rows or previous ones (previous to the ALTER TABLE ... NULL statement) then it has to update all rows with NULL during this statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
