projection_usage table not all tables got query_start_timestamp

Moderator: NorbertKrupa

Post Reply
saad.nossir
Newbie
Newbie
Posts: 2
Joined: Sun Jun 19, 2016 10:49 am

projection_usage table not all tables got query_start_timestamp

Post by saad.nossir » Sun Jun 19, 2016 10:58 am

I've been trying to develop a report , got each table name and last query date using below query
SELECT
anchor_table_id,
max( query_start_timestamp ) as last_access
FROM
v_monitor.projection_usage
group by
1
and I've noticed that not all tables got an entry in this table although i've performed a query against them , anyone experienced the same issue or can suggest another query .

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: projection_usage table not all tables got query_start_timestamp

Post by scutter » Sun Jun 19, 2016 9:11 pm

Systems tables are really views on top of Data Collector tables. So the time span for the data in the system table is dependent upon the retention of the data in the DC tables. You can see the current effective retention for each table on each node in the DATA_COLLECTOR table. You can increase the retention using a time-based policy (set_data_collector_policy()), as long as you also provide enough disk space to satisfy that time period.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: projection_usage table not all tables got query_start_timestamp

Post by NorbertKrupa » Mon Jun 20, 2016 1:07 am

You can read my post on setting a time based collection policy here.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica SQL”