ORACLE table has a large number of records, and it takes a long time to increase the field with the default value

Manpower.s_salary 375w record, add field add_sh number (7,2) default 0

1. Excluding the use of the first

SELECT P.spid,s.status,s.terminal,s.machine,s.program,s.osuser,s.client_info,s.logon_time from V$SESSION S,V$ ACCESS a,v$process P
WHERE S.sid=a.sid
and S.PADDR=P.ADDR

and a.object= ' S_salary '

2.alter table Manpower.s_salary Add (add_sh number (7,2) default 0);
It takes 21 minutes. (performed at noon hours)

2-Step implementation, the effect is significantly improved

ALTER TABLE manpower.s_salary Add (add_sh number (7,2));
It takes 0.25 seconds.

ALTER TABLE manpower.s_salary Modify (add_sh number (7,2) default 0);

It takes 0.25 seconds.

Oracle version is 10.2.05

There are still some:
1 if there is a default value in Alter SQL, Oracle refreshes all records directly.
2 There is no default value in Alter SQL, Oracle only affects subsequent records. (Understanding fast is a secret)

In choosing which method to choose, it is more specific to implement: whether there is index, whether in the high concurrency stage of the application, etc.

------------------------------------------below for reprint http://yinzhihua2008.blog.163.com/blog/static/ 7943067201211241056654/----------------------------------

Oracle data tables have a lot of data if adding fields takes a long timeAbout the way to create a faster, need to take into account, because the landlord's library is not archived mode, so the estimate is a test library.
For test environments, the landlord of the two ways to load data is feasible, I test the environment is not so large table, can only take a 15 million rows, 1G size of the table test, using data pump mode for 5 minutes (export plus import), using the Insert/*+append*/method spents 216 seconds ( If the Add new field is not null and takes 260 seconds, it seems more efficient to insert/*+append*/; but there are a few things to note:
1. In a formal production environment, the possibility of insert/*+append*/is unacceptable, given the problem of data recoverability
2. In both ways the default value for the target table is valid only for newly inserted data, not for data imported from the original table.
3. Using data pump mode, the newly added field cannot be not null
For production libraries that can accept downtime, to ensure data recoverability, it is recommended to use a data pump or a direct path loaded SQL loader to try
However, more and more Oracle is now acting as an enterprise critical database and may not be able to give enough downtime, and can be used in a highly available way, such as online redefinition.
Alternatively, follow these steps:
1. Add a new field to the table with no default value, this should be done quickly
ALTER TABLE test add test number;
2. Change this field to the default value, so that the data inserted later contains the default value, but the field value before the modification is still empty
ALTER TABLE test modify test default 88888888;
3. If you need to add the default values before the changes to the relevant historical data, you can do a batch update stored procedures, such as every 1000 commit to reduce the pressure on the production bank.
4. After the update is complete, you can set the field to NOT NULL mode
In short, in the real world of the production library, especially the 24x7 library, speed is not the only factor to consider, but also to consider the recoverability of data, the performance impact on the database during operations, and downtime, which is why Oracle offers an online redefinition that does not stand out. Sometimes in order to reduce the pressure on the production library, I deliberately add a delay to the cycle of inserting, updating, and deleting stored procedures to prevent the log from switching too frequently.
The above test is based on 10g, in 11g, it appears that Oracle is aware of this problem, so you can directly add the default value of the syntax, this update should be very short, because you find the previous data on the newly added field already has a default value, But this is shown by the Oracle internal function calculation, not real in the database, only the data added later, the default value will be inserted into the database table, so 11g you can use the following statement:
ALTER TABLE test add test number default 88888888 NOT null;