SELECT all data such that a single-node query plan is maintained

gjorgjevskigjorgjevski Registered User

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?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • gjorgjevskigjorgjevski Registered User

    Thank you, Jim! That seems to be working nicely and elegantly.

Leave a Comment

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