Re: updating all records of a table

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.