No best answer available. Please pick the good answer available or submit your answer.
April 18, 2005 04:23:22
Srikanth Addagiri
RE: When do we Analyze the tables? How do we do it?
srikanthaddagiri@yahoo.com When the data in the data warehouse changes frequently we need to analyze the tables. Analyze tables will compute/update the table statistics that will help to boost the performance of your SQL.
RE: When do we Analyze the tables? How do we do it?
In a warehouse I would recommend analyzing the data either after each load has completed or periodically (week-end or month-end) depending upon the data volumes and therefore the effort required.
You can analyze a table in Oracle at the Database Schema or Table level. I would recommend Schema or Table.
You can "estimate statistics" or "compute statistics". Personally I'd almost always use the ESTIMATE option. I've found Oracle to be around 90 accurate from an estimate but using about 20 of the effort - useful on very large tables.
Here's the scripts I typically use:-
Analyse Table: accept table_name prompt "Enter the table to analyze: " execute dbms_stats.gather_table_stats(user '&table_name' null dbms_stats.auto_sample_size null 'FOR ALL INDEXED COLUMNS SIZE 254');
