Which type of projection to use
I have a requirement as follows
1. I have three very large tables (A, B, C)
2. Table A and B are joined on a common key (a.id = b.id)
3. Table A and C and joined on a common key (a.id = c.id)
4. I want to write a query which unions the results of A->B and A->C.
5. I want to perform "group by" and "order by" on multiple columns from the joined data.
6. After grouping, we just sum, count on columns
Can I use a projection? or should I just create a new table which takes all required columns and unioned data and then perform grouping, sorting and sum/count on it.
I looked at pre-join projection.. but the documentation of 7.2 said pre-join is deprecated,
(page 56 https://my.vertica.com/docs/7.2.x/PDF/HP_Vertica_7.2.x_Complete_Documentation.pdf)
So will need your help in finding the right projection type or simply go for a new table.
Comments
If both B and C join the table A by the same key ID, you should create a projection that is segmented by ID so the join happen locally and if you also put ID first in the order by it also would do mergejoins to give you the best performance, but there other things to consider as what column are using to group by and if you have query predicates, etc. An explain plan of the query will help to see what algorithm are being used.
Or if you have samples queries run a query specific DBD and that will give you a better projection design.
Hope this helps.
Eugenia