Problem in understanding the data movement from WOS to ROS

Lets consider the below table as an example: 

 

CREATE TABLE SCHEMA.TABLE_X( ID INTEGER, NAME VERCHAR2(255)) ;

 

After creating the table we have inserted the records and when we check the total count of our table we see there are 10 rows present in the table. 

 

Then we have issued the below DMLs:

 

INSERT INTO SCHEMA.TABLE_X VALUES(1,'X');

INSERT INTO SCHEMA.TABLE_X VALUES(1,'X');

 

INSERT INTO SCHEMA.TABLE_X VALUES(1,'X');

INSERT INTO SCHEMA.TABLE_X VALUES(1,'X');

INSERT INTO SCHEMA.TABLE_X VALUES(1,'X');

COMMIT;

 

As per Vertica concepts we know, data is moved from WOS and then to ROS after the moveout operation. 

By default the moveout time is 300 secs. 

 

But in this case after we insert 5 records in our table "TABLE_X" and then after issueing the below query:

 

SELECT COUNT(1) FROM SCHEMA.TABLE_X; 

 

I could see there are in total 15 rows.

 

Now my doubt is the moveout operation is supposed to happen at an interval of 300 secs, how the data move from WOS to ROS.

 

Could you please clarify my doubt?

 

Thanks,

Kushal 

Comments

  • Hi Kushal,

     

    The query

     

    SELECT COUNT(1) FROM SCHEMA.TABLE_X

     

    will include the rows in WOS as well.

     

    Please review https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/PROJECTION_STORAGE.htm  - this explains how projection data is stored. Until a moveout occurs, the 5 rows will be stored in WOS - you can verify this by querying the projection_storage table.

     

    Hope this helps.

     

    Gayatri

  • Thanks Gayatri,

     

    "

    The query

     

    SELECT COUNT(1) FROM SCHEMA.TABLE_X

     

    will include the rows in WOS as well." 

     

    Could you please show a light where in the document its mentioned "Before moveout operation data is read from the WOS also" 

     

     

  • Vertica queries data from WOS and ROS when you do a select. You can look at the table projection_storage filter by anchor_table_name = 'table' to see how much data is in the WOS and what is in the ROS. does make sense? 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.