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
Delete Vectors
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Delete Vectors
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.
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?
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.