Hi,
Using the node "offset" option of the create projection command with the "unsegmented all nodes" option isn't supported. If you are using the "unsegmented all nodes" option you'll already be creating the projection on all nodes
I often just let Vertica do the work for me using the ksafe option of the create table and create projection statements.
Example:
I created your tables using the following statements to get the super projections created... I am assuming that tableA is a large fact table while tableB is a smaller look up table. Vertica recommends that smaller tables be unsegmented.
Code: Select all
dbadmin=> create table tableB (PK_ID int, col_B int, primary key (PK_ID)) ksafe;
CREATE TABLE
dbadmin=> create table tableA (FK_ID int not null constraint fk1 references tableB(PK_ID), col_A int) unsegmented all nodes;
CREATE TABLE
Now take a look at the super projections created:
Code: Select all
dbadmin=> select get_projections('tableA');
get_projections
----------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table testdb.tableA has 3 projections.
Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.tableA_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0002, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]
(1 row)
dbadmin=> select get_projections('tableB');
get_projections
----------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table testdb.tableB has 2 projections.
Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.tableB_b1 [Segmented: Yes] [Seg Cols: "testdb.tableB.PK_ID"] [K: 1] [testdb.tableB_b0] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableB_b0 [Segmented: Yes] [Seg Cols: "testdb.tableB.PK_ID"] [K: 1] [testdb.tableB_b1] [Safe: Yes] [UptoDate: Yes] [Stats: No]
(1 row)
Now create the pre-join projection:
Code: Select all
dbadmin=> CREATE PROJECTION if not exists projection_test
dbadmin-> (
dbadmin(> table_a_column_a,
dbadmin(> table_b_column_b
dbadmin(> )
dbadmin-> as
dbadmin-> select tableA.col_a, tableB.col_b
dbadmin-> from tableA, tableB
dbadmin-> where tableA.FK_ID = tableB.PK_ID
dbadmin-> order by tableA.col_a
dbadmin-> ksafe;
CREATE PROJECTION
Now if we check the projection for tableA we'll see three more unsegmented projections:
Code: Select all
dbadmin=> select get_projections('tableA');
get_projections
----------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table testdb.tableA has 6 projections.
Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
testdb.projection_test_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0002, testdb.projection_test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.projection_test_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0003, testdb.projection_test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.projection_test_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.projection_test_node0003, testdb.projection_test_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0002, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: No]
testdb.tableA_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [testdb.tableA_node0003, testdb.tableA_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: No]
(1 row)