Vertica v8.0 - changing data type int to decimal(39,0) in a production table with 4 billion records

Migert

Hi Vertica Experts,
I have a scenario that I need to change the datatype of a column from integer to decimal(39,0) in a production table but I am struggling to find an efficient way how to do it.

The table is in production and has 4 billion records.

I've tried the following in a development environment:

The standard alter table doesn't work:
ALTER TABLE SCHEMA.TableName alter column ColumnName set data type decimal(39,0);

Changing the data type from int to decimal(18,0) works fine but extending from decimal(18,0) to decimal(39,0) doesn't work.

Converting int to varchar doesn't work.

Any help is welcome.

Thanks
Migert

marcothesane

Try:

ALTER TABLE table_name ADD COLUMN colname_as_num DECIMAL(39,0) DEFAULT colname;

Then:

ALTER TABLE table_name ALTER COLUMN colname_as_int DROP DEFAULT;

Then:

ALTER TABLE table_name DROP COLUMN colname;

And finally:

ALTER TABLE table_name RENAME COLUMN colname_as_int TO colname;

Only works, however, if you have no projections on table_name with colname as an ORDER BY column or a SEGMENTED BY HASH() column; you would have to replace those projections with another superprojection and drop those projections first ....

Jim_Knicely

@Migert - Fyi ... I've used the method that @marcothesane described (adding a new column with a default value) on tables with 65+ billion records and it works great! Definitely the "fastest" way to accomplish your goal

Migert

Hi guys, thanks for your reply.

@marcothesane - I was thinking for a similar solution but I wasn't convinced about the step that copies the data from an int column to a decimal column.

@Jim_Knicely - thanks for sharing your experience using this method.

Just thinking about a similar scenario but converting from numeric to varchar, do you think using default cast(colname as varchar) the performance will be similar?

ALTER TABLE table_name ADD COLUMN colname_as_num VARCHAR(50) DEFAULT cast(colname as varchar);

Jim_Knicely

Hi,

I think using the "default value" method will always be the fastest route of changing a columns data type.

Not that you don't need to use the CAST function in the column default expression as Vertica will perform an implicit data type conversion for you.

Example:

dbadmin=> create table test (c1 int, c2 numeric) order by c1 segmented by hash(c1) all nodes;
CREATE TABLE

dbadmin=> insert into test select 1, 1.1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> \d public.test;
                                     List of Fields by Tables
 Schema | Table | Column |      Type      | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+----------------+------+---------+----------+-------------+-------------
 public | test  | c1     | int            |    8 |         | f        | f           |
 public | test  | c2     | numeric(37,15) |   16 |         | f        | f           |
(2 rows)

dbadmin=> insert into test select 1, 1.1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test;
 c1 |        c2
----+-------------------
  1 | 1.100000000000000
(1 row)

dbadmin=> alter table public.test add column c3 varchar(17) default c2;
ALTER TABLE

dbadmin=> alter table public.test alter column c3 drop default;
ALTER TABLE

dbadmin=> alter table public.test drop column c2;
ALTER TABLE

dbadmin=> alter table public.test rename column c3 to c2;
ALTER COLUMN

dbadmin=> \d public.test;
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | test  | c1     | int         |    8 |         | f        | f           |
 public | test  | c2     | varchar(17) |   17 |         | f        | f           |
(2 rows)

dbadmin=> select * from public.test;
 c1 |        c2
----+-------------------
  1 | 1.100000000000000
(1 row)