Disable Purge

naresh_way2naresh_way2 Registered User

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?

Comments

  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited June 27

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file