How to create a projection in vertica where I can have different sort order column for a table
Say I have a table employees with 4 column(FN,LN,SS,SAL) and I have three nodes
Can I store the different sort order on different nodes: Node1 should have (FN,LN), Node2 should have (SS) and Node3 should have (SAL)
Thanks.
Projection questions sort order
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Projection questions sort order
Yup!Can I store the different sort order on different nodes
Example:
Code: Select all
dbadmin=> create table t (fn varchar(100), ln varchAR(100), ss VARCHAR(100), sal money);
CREATE TABLE
dbadmin=> create projection pr1 as select * from t order by fn, ln unsegmented node v_verticadb_node0001;
CREATE PROJECTION
dbadmin=> create projection pr2 as select * from t order by ss unsegmented node v_verticadb_node0002;
CREATE PROJECTION
dbadmin=> create projection pr3 as select * from t order by sal unsegmented node v_verticadb_node0003;
CREATE PROJECTION
Code: Select all
dbadmin=> select pr.node_name,
dbadmin-> pc.projection_name,
dbadmin-> pc.projection_column_name,
dbadmin-> pc.column_position,
dbadmin-> pc.sort_position
dbadmin-> from projections pr
dbadmin-> join projection_columns pc
dbadmin-> on pc.projection_name = pr.projection_name
dbadmin-> where pr.projection_name in ('pr1', 'pr2', 'pr3')
dbadmin-> order by 1, 2, 4;
node_name | projection_name | projection_column_name | column_position | sort_position
---------------------+-----------------+------------------------+-----------------+---------------
v_snowfall_node0001 | pr1 | fn | 0 | 0
v_snowfall_node0001 | pr1 | ln | 1 | 1
v_snowfall_node0001 | pr1 | ss | 2 |
v_snowfall_node0001 | pr1 | sal | 3 |
v_snowfall_node0002 | pr2 | fn | 0 |
v_snowfall_node0002 | pr2 | ln | 1 |
v_snowfall_node0002 | pr2 | ss | 2 | 0
v_snowfall_node0002 | pr2 | sal | 3 |
v_snowfall_node0003 | pr3 | fn | 0 |
v_snowfall_node0003 | pr3 | ln | 1 |
v_snowfall_node0003 | pr3 | ss | 2 |
v_snowfall_node0003 | pr3 | sal | 3 | 0
(12 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 8
- Joined: Fri Nov 15, 2013 5:47 pm
Re: Projection questions sort order
This is awesome, thank you
Re: Projection questions sort order
Just because you can do it doesn't mean you should!
Every query can use only one projection per table for the entire query. This means that once the optimizer has determined which one it will use, the other ones become useless! This means that your query will mostly run on one node only if you don't distribute your data across the nodes or if you don't replicate the projection on all nodes.
Performance will not be what it should and you will see one node working like crazy and the other two doing next to nothing. This defeats the whole idea of using a cluster in the first place.
Every query can use only one projection per table for the entire query. This means that once the optimizer has determined which one it will use, the other ones become useless! This means that your query will mostly run on one node only if you don't distribute your data across the nodes or if you don't replicate the projection on all nodes.
Performance will not be what it should and you will see one node working like crazy and the other two doing next to nothing. This defeats the whole idea of using a cluster in the first place.