DIRECT Is Now the Default Load Type

Jim_Knicely

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

Sudhakar_B

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_Knicely

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)