Re: Tables Larger When using DBMS_REDEFINITION

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Fri, 20 Feb 2015 21:44:20 -0500
Message-ID: <54E7F104.8010502_at_yahoo.com>

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 [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

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


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

Received on Sat Feb 21 2015 - 03:44:20 CET