RE: Tables Larger When using DBMS_REDEFINITION
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@freelists org" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2015 23:08:07 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Michael Cunningham [napacunningham@xxxxxxxxx]
Sent: 20 February 2015 17:22
To: oracle-l@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
Other related posts:
