Change more than one column in a table in oracle

Question

Will the two scenarios below (to modify the table) make diff .. ??

script 1:

alter table ACNT_MGR_HSTRY add DM_BTNUMBER DATA_TYPE ; alter table ACNT_MGR_HSTRY add DM_BTID DATA_TYPE ; alter table ACNT_MGR_HSTRY add DM_USERID DATA_TYPE ; alter table ACNT_MGR_HSTRY add DM_WSID DATA_TYPE ; 

script 2:

 alter table ACNT_MGR_HSTRY add ( DM_BTNUMBER DATA_TYPE, DM_BTID DATA_TYPE, DM_USERID DATA_TYPE, DM_WSID DATA_TYPE ); 

will be updated, makes diff .. ???

 update OPERATIONAL_UNIT set ( BANK_ID= ENTY_CODE_ID= TIME_ZONE= DM_BTNUMBER= DM_BTID= DM_USERID= DM_WSID= ); ----------- update OPERATIONAL_UNIT set BANK_ID=; update OPERATIONAL_UNIT set ENTY_CODE_ID=; update OPERATIONAL_UNIT set TIME_ZONE=; update OPERATIONAL_UNIT set DM_BTNUMBER=; update OPERATIONAL_UNIT set DM_BTID=; update OPERATIONAL_UNIT set DM_USERID=; update OPERATIONAL_UNIT set DM_WSID=; 

Answer 1

Two examples are equivalent .

I used only statements such as in the first example ; I do not know if it is possible that you will not receive an error message if you use the second sample format in case of an error. . Gary Myers confirmed my belief:

Basically the same thing. If, for example, DM_WSID already existed, then the corresponding instruction will fail. In script 1, you will get three added columns. In script 2, you would not do that. If you have DDL or AUDIT triggers, they will fire several times for case 1. script 1 will execute several times and MAY wait for the exclusive table to lock several times.

Answer 2

Script 2 will usually work much better than script 1. Grouping such changes and making them simultaneous is almost always faster. But the real question is: is the difference significant?

Based on your comment about 50 tables of 15 columns each, I would say that the difference is at least somewhat significant and possibly very significant depending on your configuration.

Just yesterday, I made almost the same change, changing about 30 columns to about 100 tables. By running the script locally using SQL * Plus, the time is reduced from 2 minutes to 4 seconds. In most cases, communication between SQL * Plus and the database was probably spent. If you have a SQL * Plus script to run remotely, these round trips can make your script painfully slow.

Answer 3

Another way to change our columns is by copying each column that we need to change. Here Instance: -

 Alter table news modify (Newsid number primary key ) modify (newsArticleNo number check (newsArticleNo > 0)) modify (NewsArea char(15) default '' );