Altering more than one column in a table in oracle

Will the two scripts below (for altering a 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 update makes a diff..???

update OPERATIONAL_UNIT
 set ( BANK_ID=
 ENTY_CODE_ID=
 TIME_ZONE=
 DM_BTNUMBER=
 DM_BTID=
 DM_USERID=
 DM_WSID=
 );
 ---开发者_JAVA百科--------
 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=;

The two examples are equivalent.

I've only ever used statements like you have in the first example; I don't know if it's possible that you won't get as good an error message if using the second example format in the event of an error.. Gary Myers confirmed my belief:

Mostly the same. If, for example, DM_WSID already existed then the relevant statement would fail. In script 1, you'd get three of the columns added. In script 2 you wouldn't. If you have DDL triggers or AUDIT, then they will get fired multiple times for case 1. Script 1 will commit multiple times and MAY wait for an exclusive table lock several times.

Script 2 will generally perform much better than script 1. Grouping similar changes and performing them all at once is almost always faster. But the real question is, is the difference significant?

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

Just yesterday I made almost the exact same change, modifying about 30 columns for about 100 tables. Running the script locally using SQL*Plus, the time decreased from 2 minutes to 4 seconds. Most of the time was probably spent communicating between SQL*Plus and the database. If you have a SQL*Plus script that needs to be run remotely those round trips could make your script painfully slow.

One more way we can Modify our Columns by bracketing each column that we need to alter it . Here The instance :-

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