RE: Tables Larger When using DBMS_REDEFINITION

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 20 Feb 2015 23:08:07 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928272EA8_at_EXMBX01.thus.corp>

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 21 2015 - 00:08:07 CET