Will statistics affect update also?

I am doing an update on a table, but that is having very old statistics,

Whether this will affect the performance?
sakthikumarAsked:
Who is Participating?
 
Greg CloughSenior Oracle DBACommented:
More than likely the difference is that the first time you ran the update it loaded all of the rows into the cache, so you were doing much less I/O the second time.  If you're doing an update on all rows in the table then statistics won't matter, as it's always going to choose a full table scan.
0
 
dave_tillerCommented:
Statistics are considered stale when the data has changed by 20%.  At this time, by default, the auto update statistics should take care of it.  You could also manually rebuild the statistics by using the sp_updatestats stored procedure.
0
 
slightwv (䄆 Netminder) Commented:
>>using the sp_updatestats stored procedure.

That looks like a SQL Server procedure.  This was posted in an Oracle zone.

As far as the question:

What are the concerns with the update and statistics with performance?  The update statement itself of future selects/DML against  the table after the update?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Greg CloughSenior Oracle DBACommented:
Just for clarification, I presume you're asking:

"Will having old statistics on a table affect the speed of an update performed on that table?"

To which my answer is Yes, because if you've got a "WHERE" clause then the optimizer will use the statistics on the table and the indexes available to find the best query plan to return those rows that need updating. (It also uses "System Statistics", but that's another topic)  If it used to be a small table  of say 1000 rows, and now it's got 1 Billion rows but the stats are the same then Oracle will likely choose the wrong path and it will run horribly.

You can confirm this with an explain plan.
0
 
slightwv (䄆 Netminder) Commented:
>>To which my answer is Yes

Shouldn't the answer be "It might" instead of "Yes"?

Old statistics does not necessarily mean 'bad' statistics.  It depends of how much the table has changed since the stats were last generated and if those changes would affect the execution plan.
0
 
Greg CloughSenior Oracle DBACommented:
>> Old statistics does not necessarily mean 'bad' statistics.

Yes, I agree. Thanks for the clarification.  I guess I tried to explain that later in the comment, maybe with too much babble. :-)
0
 
sakthikumarAuthor Commented:
(Oracle 10g)

The table was having 3 million rows with no triggers on it.
It was having statistics collected by 2009.

I ran the update stmt to update all rows without any where clause.
It took more than 30 minutes.

After this, I collected statistics,
ran the update again. that time it took only 3 mins.

Is collection of statistics making the difference?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.