ETL - When do we Analyze the tables? How do we do it?

GeekInterview.com > Interview Questions > Data Warehousing > ETL

Go To First | Previous Question | Next Question

ETL | Question 26 of 60

When do we Analyze the tables? How do we do it?

Total Answers and Comments: 3

Last Update: May 01, 2008

Sponsored Links

Best Rated Answer

—

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.

Is this answer useful? Yes | No

September 11, 2007 09:07:28

ramana_pasula

Member Since: September 2007 Contribution: 3

RE: When do we Analyze the tables? How do we do it?

Analyze your_table_name compute statistics;

Is this answer useful? Yes | No

May 01, 2008 07:59:39

jryan999

Member Since: April 2008 Contribution: 12

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');

Analyze Schema:-
-- Note: To remove all existing statistics
-- EXEC DBMS_Stats.Delete_Schema_Stats(user);
exec dbms_stats.gather_schema_stats( -
ownname          => user, -
options          => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt       => 'for all columns size repeat', -
degree           => 15 -
);

Is this answer useful? Yes | No

Go To Top