General vertica cost-benefit query question

kfruchtman

Hi Experts!
I have a 5 billion rows main table (lets call it A) in my vertica db.
I need to add a currency amounts (euro, gbp, usd ) for every row. what would be "easier" for vertica while performing queries on this table:
1.Adding (3 calculated ) columns on the A table? Or
2.Creating a new table (B) and for each row in A have 3 columns in B ? and joining the 2 tables when query is performed?

What is the best cost -benefit vertica wise?
What would be the best way performance wise?

I hope I am clear with my question,
Many thanks in advance
Kinds regards,
Keren

kfruchtman

Anyone?

Vertica_Curtis

If my understanding is correct, you're adding 3 columns into the table. Is there a calculation that has to happen here, or are you just doing some massive update to the table?

If it's a calculation, then I would go with a projection with expressions. Basically, you'd define a projection with the 3 added columns as calculated fields. You could query this projection specifically if you need those fields, otherwise the table wouldn't really have them. Something like this:

Create table t (x int, y int) ;
insert into t values (2, 3) ;
create projection t_proj_w_expr as (x, y, z) select x, y, x + y as z from t ;
select z from t_proj_w_expr ;
6

That's pseudocode, mind you, but I think you get the idea.

Another alternative would be a flattened table. You could create a table B and then load it with relevant data, and then you'd remake table A as a Flattened table with references to B. In this way, you'd never actually have to join A to B - that definition would exist inside the table definition for A (in your new definition, that is), and those columns would appear as columns in table A, even though they exist in table B.

Those would both be less "expensive" options that just straight up adding the columns into table A, but sometimes there's no good substitute for simplicity.