Convert column from Number(9) to varchar2(3) in SQL
What is the quickest way of achieving a total column datatype change from number(9) to varchar(3)? I have a few million rows in this table and SQL will run forever ....
Software/Hardware used:
Software/Hardware used:
ASKED:
June 30, 2009 2:26 PM
UPDATED:
July 1, 2009 5:56 PM
Answer Wiki:
In Oracle, I would use the bulk collect capability, as follows:
1. Create a copy table of the source (empty with no data), as in:
create table MyClone as select * from OldTable where 0=1;
2. Alter the table to change the data type of the column from Number(9) to Varchar2(3)
alter table MyClone modify (MyColumn varchar2(3));
3. Load the table using the bulk collect:
declare
type temp_tab is table of OldTable%ROWTYPE INDEX BY PLS_INTEGER;
my_temp_table temp_tab;
begin
select * BULK COLLECT INTO my_temp_table FROM OldTable;
FORALL temp_index IN my_temp_table.FIRST .. my_temp_table.LAST
INSERT INTO MyClone VALUES my_temp_table(temp_index);
commit;
end;
4. Rename the old table (or drop it if you are very confident - grin) (also rename or drop the old indexes and other constraints)
5. Rename MyClone to OldTable
6. Apply the necessary indexes and primary keys
Voila - you are set.
A couple of notes...
Obviously, this will only work if all the values in the old Number(9) column are values that are less that 1000.
Apply the indexes and primary keys at the end of the process, as this will generally speed up the copy/bulk load.
I do not have an Oracle system handy to test this code, so there may be some minor "oops" in it. If FORALL doesn't like the implicit data conversion, the process is quite a bit more complicated, as you may need to create a separate collection for each column of data.
To see all answers submitted to the Answer Wiki: View Answer History.
I agree with Kccrosser. That could be your better option, but keep in mind that when using this approach, you are temporary storing the complete contents of the table in memory.
Depending on the available resources on the server, and the amount of data in the table, you might want to perform the bulk fetch / insert in chunks, using the LIMIT clause.
Let us know if you need further help.