Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

  • emorenoemoreno Employee

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.