Problem in understanding the data movement from WOS to ROS

kushal_alester

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 

Gayatri_R

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

kushal_alester

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" 

 

 

[Deleted User]

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