Disable Purge
Hi,
I am using vertica for 1 year. I am getting better results. But when I am doing merging operation it preserves the deleted row count. Can't we have an option to disable to save deleted row count? That means I don't want to save deleted history. How can it possible can anybody explain?
0
Comments
@Jim_Knicely
If you run make_ahm_now() prior to merge, it won't save any of the deleted rows. The AHM (ancient history mark) determines how much history to save; by moving it to now, you tell Vertica not to save any.
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/MAKE_AHM_NOW.htm?Highlight=make_ahm_now
@naresh_way2 - Per Ben's recommendation of using the MAKE_AHM_NOW() function, here is a quick example:
dbadmin=> create table test_no_dv (c int); CREATE TABLE dbadmin=> insert into test_no_dv select 1; OUTPUT -------- 1 (1 row) dbadmin=> delete from test_no_dv; OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT dbadmin=> select count(*) from delete_vectors where projection_name ilike 'test_no_dv_super'; count ------- 1 (1 row) dbadmin=> select do_tm_task('mergeout', 'test_no_dv'); do_tm_task ---------------------------------------------------------------------------------- Task: mergeout (Table: public.test_no_dv) (Projection: public.test_no_dv_super) (1 row) dbadmin=> select count(*) from delete_vectors where projection_name ilike 'test_no_dv_super'; count ------- 1 (1 row)The delete vector remains! How do I not keep it around after running a manual MERGEOUT? Use Ben's suggestion!
dbadmin=> select make_ahm_now(); make_ahm_now ------------------------------- AHM set (New AHM Epoch: 7160) (1 row) dbadmin=> select count(*) from delete_vectors where projection_name ilike 'test_no_dv_super'; count ------- 1 (1 row) dbadmin=> select do_tm_task('mergeout', 'test_no_dv'); do_tm_task ---------------------------------------------------------------------------------- Task: mergeout (Table: public.test_no_dv) (Projection: public.test_no_dv_super) (1 row) dbadmin=> select count(*) from delete_vectors where projection_name ilike 'test_no_dv_super'; count ------- 0 (1 row)