The Vertica Database Forums

Posted: **Fri Oct 21, 2016 5:59 am**

Hi guys,

I am trying to clean up my database and want to find all the unused projections. Is the following query an accurate way to find them?

bi quyet lam giau

Code: Select all

select projection_name, projection_schema, anchor_table_name from projections
where projection_name not in (select projection_name from projection_usage)
order by projection_name, projection_schema, anchor_table_name;

Posted: **Mon Oct 24, 2016 6:56 pm**

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:

The Vertica Database Forums

How do I find all on used projections?

How do I find all on used projections?

Re: How do I find all on used projections?