Hi,
Your query will show you the projections that have not been used. Well, those that have not been used based on the data available in the PROJECTION_USAGE table.
I would suggest using PROJECTION_ID in your WHERE clause:
Code: Select all
select projection_name, projection_schema, anchor_table_name from projections
where projection_id not in (select projection_id from projection_usage)
order by projection_name, projection_schema, anchor_table_name;
The PROJECTION_USAGE table has a column called QUERY_START_TIMESTAMP. You might want to take a look at the MIN value to see how far back you are looking for unused projections.
Code: Select all
dbadmin=> select min (query_start_timestamp) from projection_usage;
min
-------------------------------
2016-09-29 20:53:32.324724-04
(1 row)
You can also check the DC_PROJECTIONS_USED data collector table... You can control how long data is stored in this table by setting a policy.
Check out these links that discuss functions to help set this up: