How Delete Works in vertica

Moderator: NorbertKrupa

Post Reply
chittaranjan
Newbie
Newbie
Posts: 2
Joined: Fri May 15, 2015 7:07 pm

How Delete Works in vertica

Post by chittaranjan » Fri May 15, 2015 7:23 pm

Hi All,

I have doubt on how delete works in vertica.

I kept on Read so many articles that DELETE does not delete data from disk storage for base tables By default, delete uses the WOS.How it points to WOS i got little confused.

Exp.If i load data before 2 days so by today the data will be with in ROS with All features of Vertica(columnar,compressed).So when i fire a delete query how it saves data in WOS instead if that is the case is it goes to ROS and Marks the records ready for delete and that file stores in WOS. i can understand suppose i load today before sometime and moveout not done and if i creates a delete it may affect that data which are in WOS and uncommitted .

In that sense i am able to understand.Could you please help me how delete uses WOS while deletion if i have 1 week old data.

Thanks
chittaranjan

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

Re: How Delete Works in vertica

Post by NorbertKrupa » Sat May 16, 2015 10:32 am

Where did you read that delete uses WOS?

When a row is deleted in Vertica, it is marked for deletion and not immediately removed from storage. Remember, Vertica's file containers are write-once. If a row inside that container is removed, the entire container must be re-created. That process of consolidating containers and removing data happens at a set interval.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: How Delete Works in vertica

Post by scutter » Sun May 17, 2015 9:41 pm

Well, let’s clarify this a bit. When you delete data, a “delete vector” is created for the deleted rows. DELETE may use the WOS for the delete vector. If there are many rows being deleted, the delete vector may spill to ROS just as a load does. Or if you use the /*+direct*/ hint on the DELETE, then the delete vector will go directly to ROS.

Whether the delete vector is written to WOS or ROS is independent of whether the data being deleted is stored in the WOS or the ROS.

Here’s an example, where rows are deleted with and without the /*+direct*/ hint. You can see the two different locations for the delete vectors - DVWOS for one case and DVROS for the other.

Code: Select all

dbadmin=> create table deltest(i integer);
CREATE TABLE
dbadmin=> insert into deltest values(1);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> insert into deltest values(2);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> insert into deltest values(3);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> 
dbadmin=> select * from projections where anchor_table_name='deltest';
 projection_schema_id | projection_schema |   projection_id   | projection_name | projection_basename |     owner_id      | owner_name |  anchor_table_id  | anchor_table_name | node_id | node_name | is_prejoin | created_epoch |   create_type    | verified_fault_tolerance | is_up_to_date | has_statistics | is_segmented | segment_expression |              segment_range              | is_super_projection | has_expressions | is_aggregate_projection | aggregate_type 
----------------------+-------------------+-------------------+-----------------+---------------------+-------------------+------------+-------------------+-------------------+---------+-----------+------------+---------------+------------------+--------------------------+---------------+----------------+--------------+--------------------+-----------------------------------------+---------------------+-----------------+-------------------------+----------------
    45035996273704976 | public            | 45035996274035856 | deltest_super   | deltest             | 45035996273704962 | dbadmin    | 45035996274035854 | deltest           |       0 |           | f          |            44 | DELAYED CREATION |                        0 | t             | f              | t            | hash(deltest.i)    | implicit range: v_test_node0001[100.0%] | t                   | f               | f                       | 
(1 row)

dbadmin=> 
dbadmin=> delete from deltest where i=1;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> delete /*+direct*/ from deltest where i=2;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from delete_vectors where projection_name='deltest_super';
    node_name    | schema_name | projection_name | storage_type |      dv_oid       |    storage_oid    | deleted_row_count | used_bytes | start_epoch | end_epoch | is_sorted 
-----------------+-------------+-----------------+--------------+-------------------+-------------------+-------------------+------------+-------------+-----------+-----------
 v_test_node0001 | public      | deltest_super   | DVROS        | 45035996274035977 | 45035996274035867 |                 1 |         51 |             |           | t
 v_test_node0001 | public      | deltest_super   | DVWOS        | 45035996274035973 | 45035996274035867 |                 1 |      16384 |             |           | t
(2 rows)

Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “New to Vertica”