A blog about software development, primarily in Java and about web applications.

Thursday, June 20, 2013

Oracle Table Statistics

I traditionally used "analyze table owner.table_name compute statistics;". I believe, this analyzes the table and its indexes and checks every row rather than just a sample of rows (thus giving an accurate count in NUM_ROWS). However, Oracle now says that "analyze table" is outdated. DBMS_STATS replaces it. When I'm checking table statuses, I typically use queries such as "select table_name, num_rows, last_analyzed from user_tables where trunc(last_analyzed) = trunc(sysdate) order by 3" to find everything that has been analyzed today, in order of analysis time. I think that this sort of query also works with materialized views.

No comments: