| Add Column with default existing column [message #689857] |
Fri, 24 May 2024 03:51  |
 |
deepakdot
Messages: 91
Registered: July 2015
|
Member |
|
|
Hi ,
I want to add a new Column to a table. the New Column data should be equal to another column.
Now this is what we do.
alter table T1 add NEXT_DATE DATE ;
Update T1 SET NEXT_DATE = LAST_DATE; --( LAST_DATE is an existing column)
commit;
But this table will have huge volume of rows. So Update will take a lot of time . Is there a way where we can alter with default value as existing column. something line
alter table T1 add NEXT_DATE DATE default LAST_DATE; -- Or something else which would be faster
Regards,
Deepak Samal
|
|
|
|
|
|
|
|
|
|
| Re: Add Column with default existing column [message #689861 is a reply to message #689857] |
Fri, 24 May 2024 08:01  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:I want to add a new Column to a table. the New Column data should be equal to another column. Another solution:orclz>
orclz> select * from dept;
DEPTNO DNAME LOC
--------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
orclz> alter table dept add(newcol as (deptno*1));
Table altered.
orclz> select * from dept;
DEPTNO DNAME LOC NEWCOL
--------------- -------------- ------------- ---------------
10 ACCOUNTING NEW YORK 10
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
40 OPERATIONS BOSTON 40
orclz> |
|
|
|
| Re: Add Column with default existing column [message #689862 is a reply to message #689860] |
Fri, 24 May 2024 09:04 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You could use calculated column:
SQL> create table emp1 as select * from emp;
Table created.
SQL> desc emp1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> alter table emp1 add ename2 varchar2(10) generated always as (ename || null) not null;
Table altered.
SQL> select ename,ename2 from emp1;
ENAME ENAME2
---------- ----------
SMITH SMITH
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS ADAMS
ENAME ENAME2
---------- ----------
JAMES JAMES
FORD FORD
MILLER MILLER
14 rows selected.
SQL>
[Updated on: Fri, 24 May 2024 09:05] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Add Column with default existing column [message #689866 is a reply to message #689863] |
Fri, 24 May 2024 12:14 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, if it is only a default value that may be changed later, there is no problem at all. For several releases, Oracle does not actually update any rows when you add a column with a default value. It does it only when the row is selected.
@OP, do the test: you'll see that there is no significant redo and undo generated. It is just a DDL:orclz>
orclz> set timing on
orclz> alter table sales add (newcol varchar2(20) default 'instantaneous');
Table altered.
Elapsed: 00:00:00.09
orclz> |
|
|
|
| Re: Add Column with default existing column [message #689868 is a reply to message #689866] |
Tue, 28 May 2024 04:49 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
OP wants the new column to default to the value of another column rather than a hard-coded value.
So you can't use the actual column default functionality. Instead you need to use triggers to set the default on new rows and an update to set it on the existing rows.
@deepakdot - define huge number of rows. There are only two ways to do this - update or CTAS (create table as select). It is possible to speed up with parallel processing if that option is available to you. You can also try diy parallel - split the rows into range chunks and have different sessions do each chunk. There's a package that does that and I can't remember what it's called.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
