IDS Forum

Re: generic question: update statistics low dr....

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

In Response To: RE: generic question: update statistics low dr.... (Robert Roussey(IT))

Robert Roussey(IT) wrote:
> I thought I remember JM saying that US-medium on a table will drop
> distributions and rebuild them anyways?
>

Well, almost. Medium and HIGH both drop the existing distributions and
insert new ones in a single transaction. If the process is interrupted
the original distributions are still intact. As I said, you really only
have to explicitely DROP DISTRIBUTIONS when upgrading to make sure that
ALL of the old distributions are gone and if you do not want ANY
distributions for an object.

Art S. Kagel
Oninit, LLC

> Bob Roussey
> Unix / Informix Administration
> Spirit Airlines
> Robert.Roussey@SpiritAir.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art S. Kagel (Oninit LLC)
> Sent: Friday, November 30, 2007 9:52 AM
> To: ids@iiug.org
> Subject: Re: generic question: update statistics low dr.... [10554]
>
> 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>
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

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.