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 !