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

Abbaskhan

Hi Team,

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

Thanks,
Abbas

Vertica_Curtis

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 ;