RE: Tables Larger When using DBMS_REDEFINITION

From: <Dave.Noble_at_wolseley.com>
Date: Mon, 23 Feb 2015 08:54:30 +0000
Message-ID: <6C9971F45490794C9FEB4E7DB8F9410E0818A185_at_a04098.DS.WOLSELEY.COM>



No primary key - maybe a fact table on a DataWarehouse? Maybe something else that you haven't thought about?

And I'm afraid that "application designer should be water boarded" is extremely offensive on so many levels. Please will you keep those comments to yourself?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: 21 February 2015 02:44
To: oracle-l_at_freelists.org
Subject: Re: Tables Larger When using DBMS_REDEFINITION

One question, in addition what Jonathan has said: Why would anyone create a table without the primary key? Yes, I am aware of the "report tables", but those should be of global temporary variety. Creating table without the primary key means not having a way to distinguish between rows. That is probably the worst design offense that I have ever encountered. If a table doesn't need the primary key, than the table itself is probably unnecessary waste of storage and the application designer should be water boarded.

On 02/20/2015 06:08 PM, Jonathan Lewis 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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Michael Cunningham [napacunningham_at_gmail.com<mailto: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

--

Mladen Gogala

Oracle DBA

http://mgogala.freehostia.com

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 23 2015 - 09:54:30 CET

Original text of this message