How list of tables which are not accessed in last 6 months in vertica.

Hi Team,

Please help with sql query to find list of tables which are not accessed in last 6 months in vertica.

Thanks,
Abbas

Comments

  • select foo.table, foo.row_count, t.create_time
    , conditional_change_event(used_flag) over (partition by foo.table order by foo.table) as not_used
    from (select ps.anchor_table_name as table, ps.projection_name as projection
    , to_Char(sum(ps.ros_row_count), '999,999,999,999') as row_count
    , case when pu.projection_id is null then 0 else 1 end used_flag
    from projection_storage ps
    left join projection_usage pu using (projection_id)
    group by 1,2, pu.projection_id) foo
    join tables t on t.table_name = foo.table
    where used_flag = 0 ;

Leave a Comment

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