Delete Vectors

Moderator: NorbertKrupa

Post Reply
surendra
Newbie
Newbie
Posts: 1
Joined: Thu Sep 17, 2015 4:00 am

Delete Vectors

Post by surendra » Thu Sep 17, 2015 4:14 am

Hi

I am new to vertica can some one help me to understand how the delete vectors will be created in the below example.

I have two projections for a table and I have 1 Million deletes for that table on given date..in this case how many delete vectors will be created means it will be based on no of projections or it is nothing to do with no of projections.

--surendra

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

Re: Delete Vectors

Post by JimKnicely » Thu Sep 17, 2015 1:28 pm

Delete vectors are created for projections (the actual physical storage). In the following example, I create a simple table that has two projections. When I delete 1 row, you'll see that each projections will have a delete count of 1 in the DELETE_VECTORS system table.

Code: Select all

dbadmin=> create table test (c1 int, c2 int) order by c1;
CREATE TABLE

dbadmin=> create projection test_pr as select * from test order by c2;
CREATE PROJECTION

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

dbadmin=> insert into test values (3, 4);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> select p.projection_name, dv.node_name, deleted_row_count from projections p join delete_vectors dv on dv.projection_name = p.projection_name and p.anchor_table_name = 'test';
 projection_name | node_name | deleted_row_count
-----------------+-----------+-------------------
(0 rows)


dbadmin=> delete from test where c1 = 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> select p.projection_name, dv.node_name, deleted_row_count from projections p join delete_vectors dv on dv.projection_name = p.projection_name and p.anchor_table_name = 'test';
 projection_name |       node_name       | deleted_row_count
-----------------+-----------------------+-------------------
 test_pr         | v_testserver0_node0001 |                 1
 test_super      | v_testserver0_node0001 |                 1
(2 rows)
So in your case, each projection will have one million delete vectors. That will degrade future SQL SELECT statements. Is it possible to partition your data so that you can drop a partition instead of deleting that many records?
Jim Knicely

Image

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

Post Reply

Return to “General”