Re: updating all records of a table

robjsargent(at)gmail(dot)com (Rob Sargent) writes:
> On 03/04/2011 04:54 AM, Vibhor Kumar wrote:
>>
>> On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote:
>>
>>> On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote:
>>>> Hi:
>>>>
>>>> I have to update all the records of a table. I'm worried about
>>>> what the table will look like in terms of fragmentation when this
>>>> is finished. Is there some sort of table healing/reorg/rebuild
>>>> measure I should take if I want the resulting table to operate at
>>>> optimal efficiency? What about indexes, should I drop/recreate
>>>> those?
>>>
>>> Is it really important that it happen in one transaction?
>>>
>>> In the past when I've had to do this on large numbers of rows, I
>>> always tried to do it in batches. You can run vacuums in between
>>> groups, so that the table doesn't get too bloated.
>>>
>>> Otherwise, yeah, you're better off to do some of the cleanup Joshua
>>> suggested.
>>>
>>> A
>> +1
>>
>> If UPDATE is for all rows, then
>> 1. CTAS with change value in SELECT
>> 2. Rename the tables. -- This will give zero Bloats.
>>
>
> Elegant, but of course, this doubles the disk space consumed. Not
> always tenable.

... But if you needed to do it in one Swell Foop, there really wasn't
any other choice.

The only way *not* to double (or more) space consumption is to do
incremental updates, vacuuming around each increment.
--
select 'cbbrowne' || '@' || 'linuxdatabases.info';
http://www3.sympatico.ca/cbbrowne/lisp.html
We are MICROS~1. You will be assimilated. Resistance is futile.
(Attributed to B.G., Gill Bates)