How To Get Historical Row Count In a Table?
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
col OWNER for A10 col OBJECT_NAME for A30 col SAVTIME for A30 col ANALYZETIME for A30 col DESCRIPTION for A12 set define '~' define t_owner=~1 define t_table=~2 SELECT b.owner, b.object_name, a.savtime, a.rowcnt,a.analyzetime,'' as description FROM SYS.WRI$_OPTSTAT_TAB_HISTORY a, DBA_OBJECTS b WHERE b.owner=upper('~t_owner') and b.object_name=upper('~t_table') and b.object_type='TABLE' and a.obj#=b.object_id union select owner, table_name, last_analyzed, num_rows, last_analyzed, 'Current' from dba_tables where owner=upper('~t_owner') and table_name=upper('~t_table') order by analyzetime asc OWNER OBJECT_NAME SAVTIME ROWCNT ANALYZETIME DESCRIPTION ---------- ---------------- ------------- ---------------------- -------- ---------- ----------- HR EMP_TABLE 06-FEB-22 03.05.10.759354 AM -07:00 0 30-JAN-22 HR EMP_TABLE 13-FEB-22 03.05.25.624929 AM -07:00 50 06-FEB-22 HR EMP_TABLE 20-FEB-22 03.05.05.682130 AM -07:00 50 13-FEB-22 HR EMP_TABLE 27-FEB-22 03.05.07.798323 AM -07:00 100 20-FEB-22 HR EMP_TABLE 06-MAR-22 03.05.10.000000 AM -07:00 10000 27-FEB-22 HR EMP_TABLE 06-MAR-22 03.05.10.796727 AM -07:00 50 06-MAR-22 Current
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!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !