Data in WOS

Moderator: NorbertKrupa

Post Reply
vigneshn
Newbie
Newbie
Posts: 9
Joined: Tue Jan 06, 2015 7:56 pm

Data in WOS

Post by vigneshn » Sat Mar 07, 2015 3:08 pm

In WOS, do we have records of the table for all table's projections. If I have Super projection for a table and user defined projections [ie with only few columns] then inserting a record into table will have records for super projection and user defined projection in main memory or the data will be loaded to projection during the moveout process.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Data in WOS

Post by NorbertKrupa » Sat Mar 07, 2015 5:37 pm

In a small or trickle load, data will by default go to WOS. It could contain all the records of the table if it's a new table and the data hasn't been moved onto disk (ROS). If you have a super-projection and a specific projection, new records will go to WOS, then ROS.

Take a look at WOS, ROS, and TM and Hybrid Storage Model in the documentation.
Checkout vertica.tips for more Vertica resources.

vigneshn
Newbie
Newbie
Posts: 9
Joined: Tue Jan 06, 2015 7:56 pm

Re: Data in WOS

Post by vigneshn » Sun Mar 08, 2015 4:47 am

Thanks for your reply.

I am just reiterating . Please correct me if I am wrong, If I insert a record to a table having 10 projections then WOS will have 10 records. A record for every projection.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Data in WOS

Post by JimKnicely » Sun Mar 08, 2015 4:51 pm

Hi vigneshn,

If you do an INSERT without the DIRECT hint then, yes, typically the WOS will contain a record (i.e. row) for each projection created on that table. But depending on how the projections are defined, you could have more total rows than number of projections in support of HA.

Very simple examples:

Code: Select all

dbadmin=> create table test (col1 int) unsegmented all nodes;
CREATE TABLE
dbadmin=> create projection test_pr1 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr2 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr3 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr4 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr5 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr6 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr7 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr8 as select * from test unsegmented all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr9 as select * from test unsegmented all nodes;
CREATE PROJECTION

dbadmin=> select ps.node_name, p.projection_name, ps.wos_row_count, ps.ros_row_count, ps.row_count
dbadmin-> from projections p join projection_storage ps on ps.projection_name = p.projection_name
dbadmin-> where p.anchor_table_name = 'test'
dbadmin-> order by 1, 2;
    node_name     |  projection_name  | wos_row_count | ros_row_count | row_count
------------------+-------------------+---------------+---------------+-----------
 v_vmart_node0001 | test_node0001     |             1 |             0 |         1
 v_vmart_node0001 | test_pr1_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr2_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr3_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr4_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr5_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr6_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr7_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr8_node0001 |             1 |             0 |         1
 v_vmart_node0001 | test_pr9_node0001 |             1 |             0 |         1
 v_vmart_node0002 | test_node0002     |             1 |             0 |         1
 v_vmart_node0002 | test_pr1_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr2_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr3_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr4_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr5_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr6_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr7_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr8_node0002 |             1 |             0 |         1
 v_vmart_node0002 | test_pr9_node0002 |             1 |             0 |         1
 v_vmart_node0003 | test_node0003     |             1 |             0 |         1
 v_vmart_node0003 | test_pr1_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr2_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr3_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr4_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr5_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr6_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr7_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr8_node0003 |             1 |             0 |         1
 v_vmart_node0003 | test_pr9_node0003 |             1 |             0 |         1
(30 rows)

Code: Select all

dbadmin=> drop table test cascade;
DROP TABLE
dbadmin=> create table test (col1 int) segmented by hash(col1) all nodes;
CREATE TABLE
dbadmin=> create projection test_pr1 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr2 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr3 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr4 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr5 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr6 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr7 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr8 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION
dbadmin=> create projection test_pr9 as select * from test segmented by hash(col1) all nodes;
CREATE PROJECTION

dbadmin=> select p.projection_name, ps.node_name, ps.wos_row_count, ps.ros_row_count, ps.row_count
dbadmin-> from projections p join projection_storage ps on ps.projection_name = p.projection_name
dbadmin-> where p.anchor_table_name = 'test' and ps.wos_row_count > 0
dbadmin-> order by 1, 2;
 projection_name |    node_name     | wos_row_count | ros_row_count | row_count
-----------------+------------------+---------------+---------------+-----------
 test_b0         | v_vmart_node0002 |             1 |             0 |         1
 test_b1         | v_vmart_node0003 |             1 |             0 |         1
 test_pr1        | v_vmart_node0002 |             1 |             0 |         1
 test_pr2        | v_vmart_node0002 |             1 |             0 |         1
 test_pr3        | v_vmart_node0002 |             1 |             0 |         1
 test_pr4        | v_vmart_node0002 |             1 |             0 |         1
 test_pr5        | v_vmart_node0002 |             1 |             0 |         1
 test_pr6        | v_vmart_node0002 |             1 |             0 |         1
 test_pr7        | v_vmart_node0002 |             1 |             0 |         1
 test_pr8        | v_vmart_node0002 |             1 |             0 |         1
 test_pr9        | v_vmart_node0002 |             1 |             0 |         1
(11 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Data in WOS

Post by NorbertKrupa » Sun Mar 08, 2015 8:55 pm

Thanks for testing this out on a Sunday, Jim.
Checkout vertica.tips for more Vertica resources.

vigneshn
Newbie
Newbie
Posts: 9
Joined: Tue Jan 06, 2015 7:56 pm

Re: Data in WOS

Post by vigneshn » Tue Mar 10, 2015 6:20 pm

Thanks Jim and Norbertk for your time in explaining the concept.

I got an one more doubt what will happen if we insert with *Direct* options. From the documentation it says , the records directly move to ROS. But I feel that any data moving to Hard disk moves via Ram. In our case it is WOS. So what is the role of WOS when using Direct Option.

Thanks in Advance

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Data in WOS

Post by NorbertKrupa » Tue Mar 10, 2015 6:44 pm

vigneshn wrote: So what is the role of WOS when using Direct Option.
The role of WOS when using direct is that you're skipping WOS and going directly to ROS.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Data Load”