Will statistics affect update also?

Posted on 2012-09-19
Last Modified: 2012-09-21
I am doing an update on a table, but that is having very old statistics,

Whether this will affect the performance?
Question by:sakthikumar
    LVL 3

    Expert Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>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?
    LVL 6

    Expert Comment

    by:Greg Clough
    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>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.
    LVL 6

    Expert Comment

    by:Greg Clough
    >> 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. :-)

    Author Comment

    (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?
    LVL 6

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to recover a database from a user managed backup

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now