Re: Tables Larger When using DBMS_REDEFINITION
- From: Brent Day <coloradodba@xxxxxxxxx>
- To: napacunningham@xxxxxxxxx
- Date: Fri, 20 Feb 2015 11:24:23 -0700
I forgot to mention, using CTAS gave me a size of 43k blocks for the same
table using the same compression options.
My version is 11.2.0.4.
Brent
On Fri, Feb 20, 2015 at 11:08 AM, Brent Day <coloradodba@xxxxxxxxx> wrote:
> Yes I saw this with regular tables and using advanced compression. I
> didn't log an SR but talked to some of the internal Oracle techs I have
> access to and never got any real answers.
>
> In fact for our compression efforts we do a CTAS to get full use of
> compression. One example I can give was a small table that used 101k blocks
> and using DBMS_REDEFINITION with ADVANCED COMPRESSION for ALL OPERATIONS
> the table had 127k blocks.
>
> If you decide to log an SR let me know and I will log one as well.
>
> Brent
>
> On Fri, Feb 20, 2015 at 10:22 AM, Michael Cunningham <
> napacunningham@xxxxxxxxx> wrote:
>
>> I'm going to log a bug with Oracle on this, but has anyone experienced
>> this?
>>
>> When I use DBMS_REDEFINTION the finished size of a table is larger than
>> it was prior to redef. It looks like the problem lies in the AVG_ROW_LEN.
>> For some reason the redef code is inserting the rows into the table and the
>> AVG_ROW_LEN is larger resulting in less rows per block.
>>
>> In some cases the table is as much as 55% larger. BTW, I only see this on
>> some tables. When this does happen on a table it is consistent no matter
>> how many times I run the redef on that table.
>>
>> Also, I tested on an offending table with a simple CTAS and the size of
>> the new table is what I expect.
>>
>> --
>> Michael Cunningham
>>
>
>
Other related posts:
