|
View previous topic :: View next topic
|
| Author |
Message |
Antonio Barata
Warnings : 1
New User
Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal
|
|
|
|
Hello
I have a table that has a VARCHAR column as the last one.
Now I need to add a new column to that table. It will be a unique INDEX used very often.
The question is: Should I add it to the end or, put it just before the VARCHAR?
Thanks |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| how would you 'put it before the varchar column'? |
|
| Back to top |
|
|
Antonio Barata
Warnings : 1
New User
Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal
|
|
|
|
| Quote: |
| how would you 'put it before the varchar column'? |
I need to DROP the table, and CREATE it again. If I put it at the end, I only need an ALTER TABLE. |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi
Putting at the last will reduce lot of your efforts.
Advantages:
1) You do not need to drop the table. As you said you can add using Alter command
2) Also, if the table has data, then you will lose all the data, if you drop it.
3) If you are UNLOADing the data and after making changes to the table, you cannot load the same data in to the table using LOAD utility since a column is added in the middle.
4) Code change will be minimal. |
|
| Back to top |
|
|
Antonio Barata
Warnings : 1
New User
Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal
|
|
|
|
Thanks.
I suppose the performance will not be affected one way or the other. |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
| Yes.. Performance would not be different in either of the way. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
IBM suggests always putting VARCHAR at end. So, I image there will be some performance impact if the new column is at the end.
if nothing else, you can not always insure that you will not have a tablescan on the new (at the end) column, which by definition is in a 'variable' place. db2 can handle it, just requires a little more code.
Loading a new table, after unloading, dropping, creating is not impossible, actually it is not that hard--------otherwise no one would drop/create w/new column.
I am not a dba, you need to speak to your dba about the easiest way to repopulate your new table.
If you are really stuck, create a table under your userid that matches the old.
| Code: |
INSERT into yours
select *
from old
then drop the old, create a new,
INSERT into new
Select col1
, col2
, col3
, literal for new column
, col4
from your table.
|
When this is migrated to production, let the dba's worry about the new table. |
|
| Back to top |
|
|
Antonio Barata
Warnings : 1
New User
Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal
|
|
|
|
I'm not worried about preserving the contents of the table.
One way or the other, I will migrate the data.
My only concern is with performance.
The column will be a unique index, and will be used as a condition and also in a "order by" clause in a cursor. |
|
| Back to top |
|
|
|
|
