Is there a way to reset the table SYS.COL_USAGE$? Does the number keep going up for ever?
Of course, I can truncate the table or do DML operations but this is a SYSTEM table and I prefer not to do that.
Background: We have an unusual data warehouse setup with two databases; a warehouse database where the overnight ETL writes to and a user database which is customer facing and is cloned before the start of the day from the warehouse database. We gather stats in the warehouse database which gets copied to the user database as part of the clone.
However, I realized that SYS.COL_USAGE$, which drives the histogram creation, are based entirely on ETL queries and not user queries.
DBMS_STATS.RESET_COL_USAGE is your friend here.