| Query regarding Analyze table [message #228729] |
Wed, 04 April 2007 00:06  |
be2sp1
Messages: 52
Registered: September 2005
Location: India
|
Member |
|
|
Hi,
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.
Thanks
|
|
|
|
|
|
| Re: Query regarding Analyze table [message #228770 is a reply to message #228739] |
Wed, 04 April 2007 02:41  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
