Have you been in a situation where you had to prove the row count in a table has changed or has not changed? Or,maybe you are just curious to see how the row count changes with time in a table…

Either or, you can find the answer in the database, with minimal setup in the WRI$OPTSTAT_TAB_HISTORY.

Depending on a few factors, such as retention of statistics setup, and the frequency of the gather stats job, you could have one or multiple rows returned by the query I will show you.

When you gather statistics in the database, the current set of stats is saved to the WRI$ tables. The default retention for statistics history is 31 days, then the stats should be purged.

I will not go into details on this, it is not the scope of this post. If you have the default retention, and you gather stats every week, then you should have about 4 or 5 historical entries for a table in WRI$ table.

The thing to be aware of is the fact that the row counts are accurate as of the time stats were gathered. Everyone knows that you can’t rely 100% on the NUM_ROWS column of the DBA_TABLES.

However this is a good tool to have access to, with no setup required!

When would you run this query? Here are a few of the scenarios I used this query:

• when you need to know historically the number of rows, as an approximation
• when you are troubleshooting performance issues, i.e. plan changes
• when you are troubleshooting table changes

Do you see the difference between the SAVTIME and ANALYZETIME? SAVTIME is the time the stats were saved in the WRI$ table, and ANALYZETIME is the time the statistics were gathered!