IDS Forum

Re: generic question: update statistics low drop d

Posted By: Art S. Kagel (Oninit LLC)
Date: Friday, 30 November 2007, at 9:52 a.m.

In Response To: generic question: update statistics low drop distr (Andreas.KUTSCHE@spar.at)

Andreas.KUTSCHE@spar.at wrote:
> Hi,
>
> why should anyone run
> update statistics low drop distributions ?
> (maybe except after database upgrades - and even there it is not
> possible if the database has serveral terabytes)
>

Even on a large database you could run this table by table with far less
runtime required especially if you run several tables in parallel.

That aside, there are few situations requiring one to drop distributions
on a table, besides following a version upgrade. One is if the table is
initially nearly empty and is being loaded and must be queried without
time in between to create new distributions. Another is a load job that
may be adding large numbers of rows to a table which would skew the
actual distribution versus the saved distributions and may need to also
update large numbers of existing rows. Such a job is far more likely to
use a much less than optimal query plan for the updates with
distributions than without. Finally there are some queries and physical
distributions of keys in a table or tables which will always confuse the
optimizer. Other similar scenarios come to mind.

Dropping distributions in these situations will trigger the older OL5
style optimizer code which relies only on the stats counts in systables,
syscolumns, and sysindexes/sysindices to make reasonable decisions about
query plans and is sometimes a better choice. I've sometimes wished we
had an OPTIMIZER HINT to disable the cost based optimizer and revert to
the older one explicitely.

> What is the disadvantage of running only NEW update statistics commands:
> update statistics low ..
> update statistics medium ..
> update statistics high (for leading index columns)
>
> Regards,
> Andreas Kutsche
>
Art S. Kagel
Oninit, LLC
<SNIP>

Messages In This Thread

• generic question: update statistics low drop distr
  Andreas.KUTSCHE@spar.at -- Friday, 30 November 2007, at 3:33 a.m.
  • Re: generic question: update statistics low drop d
    Art S. Kagel (Oninit LLC) -- Friday, 30 November 2007, at 9:52 a.m.
    • RE: generic question: update statistics low dr....
      Robert Roussey(IT) -- Friday, 30 November 2007, at 10:47 a.m.
      • Re: generic question: update statistics low dr....
        Art S. Kagel (Oninit LLC) -- Friday, 30 November 2007, at 11:53 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.