Home » RDBMS Server » Performance Tuning » Query regarding Analyze table
Query regarding Analyze table [message #228729] Wed, 04 April 2007 00:06 Go to next message
Messages: 52
Registered: September 2005
Location: India

I have a table having close to 1million records in it. There is another procedure that runs on this table on a weeklly basis and picks out data to upload in some other table after doing some modifications to the extisting data. This copying of data takes quite a lot of time. I would like to know if the query 'Analyze table compute statistics' could be of any help in this scenario. Does analyzing the table help in fast read of data from the table.

Re: Query regarding Analyze table [message #228739 is a reply to message #228729] Wed, 04 April 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Optimize your code.
Don't do it slow by slow (record by record).
If you can do it in SQL do it in SQL not PL/SQL.

Re: Query regarding Analyze table [message #228770 is a reply to message #228739] Wed, 04 April 2007 02:41 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ANALYZE TABLE is the old syntax, you should be using DBMS_STATS.GATHER_TABLE_STATS instead.

As to whether it will improve the performance, it depends on a lot of things. Statistics help the optimizer choose the appropriate access methods (eg. full scan or index access), join methods (eg. nested loops or hash join), plus some other minor things. If the optimizer is choosing poor access or join methods, then up-to-date accurate statistics might help.

However if the optimal plan requires an index that does not exist, stats are not going to help you. Equally, if your SQL contains expressions or constructs that enforce a sub-optimal plan, stats still won't help.

Ross Leishman
Previous Topic: tuning of plsql program
Next Topic: Procedure Cost Problem
Goto Forum:

Current Time: Fri Aug 18 14:58:39 CDT 2017

Total time taken to generate the page: 0.15112 seconds