Re: Tables Larger When using DBMS_REDEFINITION

From: Michael Cunningham <napacunningham_at_gmail.com>
Date: Fri, 20 Feb 2015 20:36:33 -0800
Message-ID: <CAPt39tvEjigQtZKbnzO701EE+7xDfWb55LqUULrFsHZk8LzG4g_at_mail.gmail.com>

Sorry for the late reply. Well, once again this forum has proven invaluable in coming to the aid of us mere mortals. Thanks Jonathan, that certainly spells it out clearly and give me something to look at in fixing my issues.

I really do appreciate all the help everyone responded with.

Michael

On Fri, Feb 20, 2015 at 3:08 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> Online redefinition depends on materialized view technology and in your
> example has to add a column to your table as it copies it to hold the
> original rowid; this column is ultimately set to unused:
>
>
> SQL> select table_name, column_name, data_type, data_length,
> hidden_column from user_tab_cols order by table_name, column_id;
>
> TABLE_NAME COLUMN_NAME DATA_TYPE
> DATA_LENGTH HID
> -------------------- --------------------------------
> -------------------------------- ----------- ---
> REDEF_TABLE_SIZE1 ID_1 NUMBER
> 22 NO
> ID_2 NUMBER
> 22 NO
> ID_3 NUMBER
> 22 NO
>
> REDEF_TABLE_SIZE2 ID_1 NUMBER
> 22 NO
> ID_2 NUMBER
> 22 NO
> ID_3 NUMBER
> 22 NO
> SYS_C00004_15022022:38:27$ VARCHAR2
> 255 YES
>
>
> The stored rowid is 18 characters - which is why it's so noticeable for
> your example which starts with an avg_row_len of 13.
> It's part of the implementation, not a bug.
>
> If you create a primary key constraint on the table this rowid column
> won't be needed as the materialized view log will be "using PK"
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Michael Cunningham [napacunningham_at_gmail.com]
> *Sent:* 20 February 2015 17:22
> *To:* oracle-l_at_freelists org
> *Subject:* Tables Larger When using DBMS_REDEFINITION
>
> 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
>

-- 
Michael Cunningham

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 21 2015 - 05:36:33 CET