Options
SELECT all data such that a single-node query plan is maintained
Assume I have a table defined as
CREATE TABLE checkout (
id INT ENCODING RLE,
transaction_timestamp TIMESTAMP ENCODING DELTAVAL,
transaction_amount DOUBLE PRECISION
)
ORDER BY transaction_timestamp
SEGMENTED BY hash(id) ALL NODES;
My Vertica cluster has 3 nodes. I want to go to one of those nodes and SELECT * FROM checkout; such that the data is present locally and the query is executed only on that node. Is there a way to do this?
0
Comments
Hi,
Is this what you need?
dbadmin=> CREATE TABLE checkout ( dbadmin(> id INT ENCODING RLE, dbadmin(> transaction_timestamp TIMESTAMP ENCODING DELTAVAL, dbadmin(> transaction_amount DOUBLE PRECISION dbadmin(> ) dbadmin-> ORDER BY transaction_timestamp dbadmin-> SEGMENTED BY hash(id) ALL NODES; CREATE TABLE dbadmin=> INSERT INTO checkout SELECT 1, sysdate, 100; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO checkout SELECT 2, sysdate+2, 200; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO checkout SELECT 3, sysdate+3, 300; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO checkout SELECT 4, sysdate+4, 400; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO checkout SELECT 5, sysdate+5, 500; OUTPUT -------- 1 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> SELECT node_name FROM nodes; node_name -------------------- v_test_db_node0001 v_test_db_node0002 v_test_db_node0003 (3 rows) dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0001' ORDER BY id; id | transaction_timestamp | transaction_amount ----+---------------------------+-------------------- 5 | 2018-10-10 09:46:53.97863 | 500 (1 row) dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0002' ORDER BY id; id | transaction_timestamp | transaction_amount ----+----------------------------+-------------------- 1 | 2018-10-05 09:46:53.756176 | 100 2 | 2018-10-07 09:46:53.820046 | 200 3 | 2018-10-08 09:46:53.870484 | 300 (3 rows) dbadmin=> SELECT * FROM checkout WHERE local_node_name() = 'v_test_db_node0003' ORDER BY id; id | transaction_timestamp | transaction_amount ----+----------------------------+-------------------- 4 | 2018-10-09 09:46:53.922143 | 400 (1 row)Thank you, Jim! That seems to be working nicely and elegantly.