Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

analyze table after insert a large number of records?

Status
Not open for further replies.

kknight2046

Programmer
Jul 6, 2006
17
US
For performance purpose, is it a good practice to execute an 'analyze table' command after inserting a large number of a records into a table in Oracle 10g, if there is a complex query following the insert?

For example:

Insert into foo ...... //Insert one million records to table foo.

analyze table foo COMPUTE STATISTICS; //analyze table foo

select * from foo, bar, car...... //Execute a complex query whithout hints
//after 1 million records inserted into foo

Does this strategy help to improve the overall performance?

Thanks.
 
Hi, try to avaoid using Analyze. The accepted method nowadays is to use the dbms_stats package. You should generally gather stats on tables that have changed by 10% or more (I don't have the source of this figure at the moment but I do recall reading it in several P & T articles) Remember, you can gather stats on a sample of the data instead of all the data if you want to make the process a bit quicker.
Finally, yes, as a general rule, gathering stats will improve the performance of queries, but this is not cast in stone (Nothing ever is when it comes to performance and tuning) Check out the excellent P&T manual @
You'll need an oracle login to download it (free and quick to register) or you can view it online without a login.
 
Thank you for your replay, jimirvine!

What's the full name of P&T manual? I searched it on tahiti.oracle.com, but didn't find it? What is the keyword I should use to search?

Thanks.
 
Hi, you are looking for the Performance and tuning guide. Happy reading :)
 
The Oracle PL/SQL Packages and Types Reference book has all the details and syntax.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top