Comments

• naresh_way2

  June 2018

  @Jim_Knicely

  -1
• Ben_Vandiver Employee

  June 2018

  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

  0
• Jim_Knicely - Select Field - Administrator

  June 2018 edited June 2018

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

  0