Disable Purge

naresh_way2

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?

naresh_way2

@Jim_Knicely

Ben_Vandiver

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

Jim_Knicely

@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)