Thursday, August 25, 2011

Updating the NUM_ROWS in USER_TABLES in Oracle

Good or bad, I use the NUM_ROWS values in the USER_TABLES table to help filter lists of tables when presenting them to the user. If I have a database and only about 25% of tables have records (and there are thousands of tables), then it is a lot nicer to only sift through the tables that do have records, and knowing how many records is even better (even if it is only an estimate). My problem is that these values don't just get updated automatically for you. You might find that they are all 0 even when there is data present. You can update all of the values with something like this:

exec dbms_stats.gather_schema_stats('mySchema', cascade=>TRUE)

A lot more info on this topic can be found at:

http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm