analyze table after insert a large number of records?

[kknight2046]

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.

 

[jimirvine]

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 @

http://tahiti.oracle.com

You'll need an oracle login to download it (free and quick to register) or you can view it online without a login.

 

[kknight2046]

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.

 

[jimirvine]

Hi, you are looking for the Performance and tuning guide. Happy reading

 

[evatype2]

The Oracle PL/SQL Packages and Types Reference book has all the details and syntax.