DIRECT Is Now the Default Load Type

Write Optimized Store (WOS) is a memory-resident data structure for short-term data storage while Read Optimized Store (ROS) is a highly optimized, read-oriented, disk storage structure, organized by projection.

Prior to Vertica 9.3, by default, Vertica initially loads data into WOS.

For databases created in version 9.3 and later, Vertica now uses a default load type of DIRECT. This setting loads data directly to ROS, bypassing WOS. Databases created in earlier versions are not affected.

Example:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v8.1.1-27
(1 row)

dbadmin=> CREATE TABLE test1 (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT /*+ TRICKLE */ INTO test1 SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes, ros_count
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_name = 'test1'
dbadmin->  ORDER BY GREATEST(wos_row_count, ros_row_count) DESC
dbadmin->  LIMIT 1;
 wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes | ros_count
---------------+----------------+---------------+----------------+-----------
             1 |          16384 |             0 |              0 |         0
(1 row)

Note that in the above example, using Vertica 8.1.1-27, the WOS columns have values > 0.

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.3.0-0
(1 row)

dbadmin=> CREATE TABLE test1 (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT /*+ TRICKLE */ INTO test1 SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT wos_row_count, wos_used_bytes, ros_row_count, ros_used_bytes, ros_count
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_name = 'test1'
dbadmin->  ORDER BY GREATEST(wos_row_count, ros_row_count) DESC
dbadmin->  LIMIT 1;
 wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes | ros_count
---------------+----------------+---------------+----------------+-----------
             0 |              0 |             1 |             79 |         1
(1 row)

Note that in the above example, using Vertica 9.3.0, the ROS columns have values > 0.

Have fun!

Helpful Links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Glossary/WOSWriteOptimizedStore.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Glossary/ROSReadOptimizedStore.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/ChoosingALoadMethod.htm

Comments

  • WOW Finally!!!!
    I always wondered why Vertica didn't do this. I have 9.2.x not 9.3.x yet.
    This is awesome news, as I am getting tired of telling developers to use the hint.
    Thanks Jim.

  • Jim_KnicelyJim_Knicely Administrator
    edited October 2019

    Note that this a feature of "new " databases created after installing 9.3. If you want an existing DB upgraded to 9.3 to use this feature, change the value of the DMLTargetDirect database parameter.

    dbadmin=> SELECT current_value, description FROM configuration_parameters WHERE parameter_name = 'DMLTargetDirect';
     current_value |                                  description
    ---------------+--------------------------------------------------------------------------------
     1             | if set to false dmls will default to AUTO and storage hints will be considered
    (1 row)
    
Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.