Shulou(Shulou.com)06/01 Report--

In oracle 11gR2 version, adding fields with default values to large tables needs to be split into multiple steps, otherwise the table will be locked for a long time. As shown below:

Adding fields with default values to a table of 2.6 million data takes more than 2 minutes.

Our standard practice steps are:

(1) Additional fields

alter table T_ORDER add tstatus varchar2(20);

(2) Batch update data

declare

n_count number;

begin

select ceil(count(1)/100000) into n_count

from T_ORDER where tstatus is null;

for i in 1.. n_count loop

update T_ORDER set tstatus='1' where tstatus is null and rownum select count(1) from test;

COUNT(1)

----------

3461376

Oracle11gR2 Version:

Session 1 Session 2 Conclusion 1: In Oracle 11gR2 version, when adding columns and modifying column defaults, if there are no uncommitted ddl and dml operations in other sessions, they can be completed instantly. SQL> set timing on

SQL> alter table test add col2 varchar2(10) ;

Table altered.

Elapsed: 00:00:00.00

SQL>

SQL>

SQL> alter table test modify col2 default '1';

Table altered.

Elapsed: 00:00:00.00

SQL>

SQL> select count(1) from test where col2='1';

COUNT(1)

----------

0

Elapsed: 00:00:00.04

Conclusion 2: In oracle11gR2, directly add columns with default values, execution time and table data volume related SQL> alter table test add col3 varchar2(10) default '1';

Table altered.

Elapsed: 00:01:49.02

SQL> SQL> SQL>

SQL> alter table test add col4 date default sysdate;

Table altered.

Elapsed: 00:02:04.62

Conclusion 3: Adding a column with default values results in an error (failure to acquire exclusive lock) when a DML operation is not committed. Adding columns without defaults waits for the dml operation to commit (releasing row-level locks) before execution completes.

SQL> set time on

15:17:50 SQL> delete from test where rownum=1;

1 row deleted.SQL> set time on

15:18:11 SQL> alter table test add col5 varchar2(10) default '1';

alter table test add col5 varchar2(10) default '1'

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:00.00

15:18:16 SQL> alter table test add col5 varchar2(10);

15:17:54 SQL> commit;

Commit complete.

15:18:43 SQL>Table altered.

Elapsed: 00:00:20.35

15:18:43 >

Conclusion 4: When adding default value with default value and non-null constraint, if there is no DML block, it can be completed instantly; if there is DML operation not committed, it needs to wait until DML operation is committed. 15:24:50 SQL> alter table test add col6 varchar2(10) default '1' not null;

Table altered.

Elapsed: 00:00:00.01

15:27:55 SQL> delete from test where rownum=1;

1 row deleted.

15:28:01 SQL> alter table test add col7 varchar2(10) default '1' not null;

15:28:47 SQL> commit;

Commit complete.

15:29:04 SQL>Table altered.

Elapsed: 00:00:09.27

15:29:04 SQL>

Oracle 19c version of the experimental preparation operation is the same, the data volume is basically the same

SQL> SQL> select count(1) from test;

COUNT(1)

----------

3479400

Session 1 Session 2 Conclusion 5: In version 19c, adding columns with default values and non-null constraints can be done instantly. If there is a DML operation that is not finished, you still have to wait for the operation to finish before you can finish. SQL> set timing on

SQL> alter table test add col3 varchar2(10) default '1';

Table altered.

Elapsed: 00:00:00.01

SQL>

SQL> alter table test add col4 date default sysdate;

Table altered.

Elapsed: 00:00:00.02

SQL> set time on

15:43:01 SQL> delete from test where rownum=1;

1 row deleted.

15:43:07 SQL> alter table test add col5 varchar2(10) default '1';

15:43:03 SQL> commit;

Commit complete.

15:43:24 SQL>Table altered.

Elapsed: 00:00:05.76

15:43:24 SQL> 15:43:24 SQL>

In the official document of 19c, it is described as follows:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-and-Manual-Locking-Mechanisms-During-SQL-Operations.html#GUID-1B08DE66-5ED8-4BEF-893B-B887E3A82D50

literally translated as

11.2 The alter table add column with default value operation in the version does not block normally, but it is demoted to a blocking operation when supplementary log is used.

To actually test this operation, turn on the minimum auxiliary log for the database, add supplementary log data(all) columns for the table (alter table testu.test2;), and then use OGG synchronization for the test table. Operations can be done instantly. I don't understand it yet. I'll add it later if there is progress.

Summary:

When adding columns with default values in version 11gR2, you need to specify the not null attribute to complete it instantly; otherwise, the table lock time is longer.

When adding columns with default values in version 19c, it was done instantly, regardless of whether it contained the not null attribute.

The operation needs to be performed during the low peak period of the service to avoid the blocking caused by dml operation during the operation.

Comprehension:

DML operations in the database mainly need to avoid problems such as long locking time caused by large transactions, huge resources such as redo and undo, uncontrollable rollback operations when resources are insufficient, etc.

In addition to the operation time, DDL operations also need to consider the blocking problem of table exclusive locks on other operations.

As the database version iterates and the functionality improves, many lessons no longer apply. Before applying the new version of the database, the specification and operation manual should be tested to keep pace with the times and improve efficiency.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.