RE: Tables Larger When using DBMS_REDEFINITION
- From: <Dave.Noble@xxxxxxxxxxxx>
- To: <dmarc-noreply@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Feb 2015 08:54:30 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: 21 February 2015 02:44
To: oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Michael Cunningham
[napacunningham@xxxxxxxxx<mailto: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
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
Other related posts:
