I'll guess -- hypothesize....

The life of a row in this table starts with the row being skinny.  Makes sense, based on the column names -- the rows look like they store history so when first inserted -- most all of the trailing columns (the history) are NULL.

So, you insert a bunch of skinny rows....  You get hundreds / block.  Now, you start modifying these rows over time -- adding the history as the present becomes the past.  The row gets fatter (hey, sort of like us, the older we are...).  Now, where 100 rows used to fit -- only 6 or 7 do (avg rowsize = 500 bytes over time, 4k blocks 3500 bytes and its full).  the other 95 or so rows MIGRATE to other blocks to live.  No more room for them on the block.

So, now that you know that about 6/7 rows fit per block on average here -- what should you do?  Well, you should set the PCTFREE much much higher so that only 6/7 rows can get inserted per block.  Consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y varchar2(4000) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t
  2  select object_id, null
  3    from all_objects
  4   where rownum <= 662*3;

1986 rows created.

<b>I picked 662*3 cause I knew I would get 662 rows / block on my system and I wanted just 3 blocks of data to start... We'll end up with 7 rows/block soon</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> select dbms_rowid.rowid_block_number(rowid), count(*)
  2    from t
  3   group by dbms_rowid.rowid_block_number(rowid)
  4  /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                  74        662
                                  75        662
                                  76        662

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select CHAIN_CNT,
  2         round(chain_cnt/num_rows*100,2) pct_chained,
  3         AVG_ROW_LEN, PCT_FREE , PCT_used
  4    from user_tables
  5   where table_name = 'T'
  6  /

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         0           0           8         10         40

<b>No chaining of course.  Our rows started small, so now we update them</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set y = rpad( '*', 1000, '*' );
1986 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select CHAIN_CNT,
  2         round(chain_cnt/num_rows*100,2) pct_chained,
  3         AVG_ROW_LEN, PCT_FREE , PCT_used
  4    from user_tables
  5   where table_name = 'T'
  6  /

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
      1986         100        1017         10         40

<b>There we go, we have the worst case, all of them moved -- all of them!</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t move;
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select dbms_rowid.rowid_block_number(rowid), count(*)
  2    from t
  3   group by dbms_rowid.rowid_block_number(rowid)
  4  /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                               53834          7
                               53835          7
                               53836          7
                               53837          7
...
                               54115          7
                               54116          7
                               54117          5

284 rows selected.

<b>after unchaining the table -- we see -- 7 rows/block basically.  And none of them are chained (they all fit on the block itself</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select CHAIN_CNT,
  2         round(chain_cnt/num_rows*100,2) pct_chained,
  3         AVG_ROW_LEN, PCT_FREE , PCT_used
  4    from user_tables
  5   where table_name = 'T'
  6  /

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         0           0        1011         10         40


<b>So, armed with that knowledge, what can you do?  Well, if you KNOW you'll insert lots of rows with little data and be updating it over time, you can reserve most of the blocks for these updates.  I played around a bit to get the right pctfree</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  ( x int, y varchar2(4000) )
  3  pctfree 99 pctused 1;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t
  2  select object_id, null
  3    from all_objects
  4   where rownum <= 35;

35 rows created.

<b>and put in 5 blocks of data..</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select dbms_rowid.rowid_block_number(rowid),
count(*)
  2    from t
  3   group by dbms_rowid.rowid_block_number(rowid)
  4  /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                  74          7
                                  75          7
                                  76          7
                                  77          7
                                  78          7

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select CHAIN_CNT,
  2         round(chain_cnt/num_rows*100,2) pct_chained,
  3         AVG_ROW_LEN, PCT_FREE , PCT_used
  4    from user_tables
  5   where table_name = 'T'
  6  /

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         0           0           8         99          1

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set y = rpad( '*', 1000, '*' );

35 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select CHAIN_CNT,
  2         round(chain_cnt/num_rows*100,2) pct_chained,
  3         AVG_ROW_LEN, PCT_FREE , PCT_used
  4    from user_tables
  5   where table_name = 'T'
  6  /

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
---------- ----------- ----------- ---------- ----------
         0           0        1011         99          1

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>

<b>and now you can see there is no migration -- the rows can expand.  I've set the space aside in the beginning</b>

So, what should you do?  I would recommend an alter table MOVE to unmigrate these rows (and then alter index rebuilds as well -- since the indexes will be invalid after this operation).  AFTER the alter table move, you would do an ALTER TABLE to up the pctfree setting for all subsequent inserts (you don't necessarily want to play with the pctfree on the move -- if the existing rows are as "fat" as they are going to be -- on the other hand, if the existing rows are only about "half as fat" as they are going to get -- you could use a higher pctfree on the alter table move, like 40% or 50% for their updates)

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t pctfree 75
  2  ;

Table altered.


 

max size is not 3 -- it is much larger (numbers are varying width strings)

ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> insert into t select object_id from all_objects;

30540 rows created.

ops$tkyte@ORA920> insert into t values ( rpad('9',38,'9') );

1 row created.

ops$tkyte@ORA920> insert into t values ( '-'||rpad('9',38,'9') );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select max(to_number(rtrim(substr( dump(x), instr(dump(x),'Len=')+4, 2 ),':'))) d,
  2         max(vsize(x))
  3    from t;

         D MAX(VSIZE(X))
---------- -------------
        21            21



but anyway -- null/not null flags, length bytes, some row overhead.


 

which could have been different, altered.


No, I do not see how simple row chaining could account for a 400meg down to 35meg occurrence.

Something else is as work here -- storage parameters were different or someone allocated many more extents manually or the data actually was deleted or the data was allowed to grow really big and then shrunk back (i'll demo that one below) -- but without an example, or all of the details, we cannot really say what.


Consider:

ops$tkyte@ORA920> create table t
  2  ( a varchar2(4000) default rpad('*',4000,'*'),
  3    b varchar2(4000) default rpad('*',4000,'*'),
  4    c varchar2(4000) default rpad('*',4000,'*'),
  5    d varchar2(4000) default rpad('*',4000,'*'),
  6    e varchar2(4000) default rpad('*',4000,'*'),
  7    f varchar2(4000) default rpad('*',4000,'*'),
  8    g varchar2(4000) default rpad('*',4000,'*'),
  9    h varchar2(4000) default rpad('*',4000,'*'),
 10    i varchar2(4000) default rpad('*',4000,'*'),
 11    j varchar2(4000) default rpad('*',4000,'*')
 12  );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t (a)
  2  select rpad('*',4000,'*')
  3    from all_objects
  4   where rownum <= 1000;

1000 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec show_space( 'T' );
Free Blocks.............................
Total Blocks............................6144
Total Bytes.............................50331648
Total MBytes............................48
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................9
Last Used Ext BlockId...................118664
Last Used Block.........................128

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> update t set b='x', c='y', d='z', e=null, f='a',g=1,h=2,i=3,j=4;

1000 rows updated.

ops$tkyte@ORA920> drop table t2;

Table dropped.

ops$tkyte@ORA920> create table t2 as select * from t;

Table created.

ops$tkyte@ORA920> exec show_space( 'T2' )
Free Blocks.............................
Total Blocks............................1024
Total Bytes.............................8388608
Total MBytes............................8
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................9
Last Used Ext BlockId...................102024
Last Used Block.........................128

PL/SQL procedure successfully completed.


So, T was 45meg -- T2 is 8meg.... same data - why?  because T got really fat, then really skinny but a table never decreases until re-orged so....  T2 is smaller than T 

what you'll have after the rebuild it

a) nothing really on the freelist
b) blocks that are 80% full (20% for growth)
c) any newly added block (from above HWM) will be 60% free space after inserts
d) pctused will govern when and if a block from b) goes back onto the freelist.

yes, it would work (assuming no ASSM in place for this example:

ops$tkyte@ORA9IR2> create table t ( x int, y char(50) ) pctfree 20;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select rownum, 'x' from all_objects where rownum <= 323;
 
323 rows created.
 
ops$tkyte@ORA9IR2> select dbms_rowid.rowid_block_number(rowid) , count(*) from t
  2  group by dbms_rowid.rowid_block_number(rowid) order by 1;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                  10        109
                                  11        107
                                  12        107
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
<b>that represents your "after rebuild" state</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t pctfree 60;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select -rownum, 'x' from all_objects where rownum <= 323;
 
323 rows created.
 
ops$tkyte@ORA9IR2> select dbms_rowid.rowid_block_number(rowid) , count(*) from t
  2  group by dbms_rowid.rowid_block_number(rowid) order by 1;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                  10        109
                                  11        107
                                  12        107
                                  13         53
                                  14         53
                                  15         52
                                  16         52
                                  17         52
                                  18         52
                                  19          9
 
10 rows selected.
 
ops$tkyte@ORA9IR2> select dbms_rowid.rowid_block_number(rowid) , count(*) from t
  2  where x < 0
  3  group by dbms_rowid.rowid_block_number(rowid) order by 1;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                  13         53
                                  14         53
                                  15         52
                                  16         52
                                  17         52
                                  18         52
                                  19          9
 
7 rows selected.
 
<b>newly inserted data will use the pctfree 60, old data blocks will be pctfree 20</b> 

without a test case I and the rest of the world can run to reproduce, I'll have to fall back on "a hunch"

your table has 282 columns

the trailing columns are NULL -- only columns 1..255 or less actually have a value in your 20 row example.

trailing null columns in a table take ZERO bytes.

You added a new "last column" and updated it to not null.

so, the table has a 283'rd column.  the intervening columns now all take 1 byte (for the null flag, they go from zero to 1).

Now, the rows are truly chained since every table with more than 255 columns such that the last column(s) are not null (some column over 255) 


here be the example:

ops$tkyte@ORA9IR2> declare
  2          l_stmt long := 'create table t ( x int primary key';
  3  begin
  4          for i in 1 .. 250
  5          loop
  6                  l_stmt := l_stmt || ', c' || i || ' number default 1';
  7          end loop;
  8          for i in 251..281
  9          loop
 10                  l_stmt := l_stmt || ', c' || i || ' number ';
 11          end loop;
 12          execute immediate l_stmt || ')';
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> insert into t (x) values ( 1);
 
1 row created.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> select avg_row_len, BLOCKS, chain_cnt, PCT_FREE, PCT_used from user_tables
  2  where table_name = 'T';
 
AVG_ROW_LEN     BLOCKS  CHAIN_CNT   PCT_FREE   PCT_USED
----------- ---------- ---------- ---------- ----------
        756          5          0         10
 
<b>nothing chained, row length of 756..</b>

ops$tkyte@ORA9IR2> update t set c281 = 42;
 
1 row updated.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> select avg_row_len, BLOCKS, chain_cnt, PCT_FREE, PCT_used from user_tables
  2  where table_name = 'T';
 
AVG_ROW_LEN     BLOCKS  CHAIN_CNT   PCT_FREE   PCT_USED
----------- ---------- ---------- ---------- ----------
        795          5          1         10
 
<b>1 row chained -- because of the trailing column being NOT NULL - and avg row length goes up</b>


 

tables with more than 255 columns are always stored in multiple row pieces, yes.


looks like an "issue with chain_cnt" for the row is chained (if you wanted to go as gory deep as dumping the block -- you could see it physically).  I can show it using the statistic 'table fetch continued row'

picking my example up where I left off:


ops$tkyte@ORA9IR2> alter table t move;
Table altered.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
Table analyzed.
 
ops$tkyte@ORA9IR2> select avg_row_len, BLOCKS, chain_cnt, PCT_FREE, PCT_used
  2   from user_tables
  3  where table_name = 'T';
 
AVG_ROW_LEN     BLOCKS  CHAIN_CNT   PCT_FREE   PCT_USED
----------- ---------- ---------- ---------- ----------
        795          1          0         10         40

<b>according to that -- no chaining, but:</b>

 
ops$tkyte@ORA9IR2> @mystat continued
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> define S="&1"
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('continued')||'%'
 
NAME                                VALUE
------------------------------ ----------
table fetch continued row               6
 
ops$tkyte@ORA9IR2> select c1 from t;
 
        C1
----------
         1
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   1: select a.name, b.value V, b.value-&V diff
new   1: select a.name, b.value V, b.value-         6 diff
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('continued')||'%'
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
table fetch continued row               6          0

<b>fetching c1 incurred no table fetch continued row, but getting this column</b>
 
ops$tkyte@ORA9IR2> select c281 from t;
 
      C281
----------
        42
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&S')||'%'
  5  /
old   1: select a.name, b.value V, b.value-&V diff
new   1: select a.name, b.value V, b.value-         6 diff
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('continued')||'%'
 
NAME                                    V       DIFF
------------------------------ ---------- ----------
table fetch continued row               7          1

<b>does, the row is in fact chained -- for whatever reason, the analyze did not report it.  What i observed physically is that

a) in the first case, the row was physically chained over two blocks
b) in the second case, the row was physically chained *on the same block*

so the chain_cnt must be a count of rows that are chained over 2 or more blocks as well as migrated rows -- but not the special case of a row stored in multiple row pieces on the same block and the alter table t move put the two row pieces on the same block. 

</b>


So, before the move, it took two IO's of two different block to construct the row, after it took two IO's of the *same* block.


As for the table in production -- that is going to be a problem.  It is going to get "fatter" and it'll probably cause many of the rows to now physically span two blocks.

I would probably *not* alter this table and add a new column with a default -- If the column is not null and frequently accessed, I would:

  1  create table
  2  new_table
  3  ( x, c1, c2, new_column not null, c3, c4 )
  4  as
  5* select x, c1, c2, cast( 'x' as varchar2(20) ), c3, c4 from t
ops$tkyte@ORA9IR2> /
 
Table created.
 
ops$tkyte@ORA9IR2> alter table new_table modify new_column default 'x';
 
Table altered.
 
ops$tkyte@ORA9IR2> desc new_table;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 C1                                                NUMBER
 C2                                                NUMBER
 NEW_COLUMN                               NOT NULL VARCHAR2(20)
 C3                                                NUMBER
 C4                                                NUMBER
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> rename new_table to t;
 
Table renamed.


placing this NOT NULL column up in the first 255 columns -- letting the trailing columns remain trailing NULL COLUMNS (might even look at the table as it exists and determine the optimum set of columns to have "trailing" -- the ones that are most frequently all NULL).

If the order of columns was 'relevant' to someone I would:

create table new_table as select ...
drop table t;
create view t as select <columns in any order you like> from new_table;


 

No.

not at all.


you are counting the HEAD rowid piece.

rows can chain, rows can migrate, rows can be deleted...



ops$tkyte@ORA10GR2> create table t ( x int ) tablespace manual;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t
  2  select null from all_objects;

49906 rows created.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select blocks, (select count(distinct dbms_rowid.rowid_block_number(rowid)) from t) from user_tables where table_name = 'T';

    BLOCKS (SELECTCOUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))FROMT)
---------- ----------------------------------------------------------------
        76                                                               76

ops$tkyte@ORA10GR2> update t set x = rpad('9',38,'9');

49906 rows updated.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select blocks, (select count(distinct dbms_rowid.rowid_block_number(rowid)) from t) from user_tables where table_name = 'T';

    BLOCKS (SELECTCOUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))FROMT)
---------- ----------------------------------------------------------------
       433                                                               76


ops$tkyte@ORA10GR2> delete from t;

49906 rows deleted.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2>  select blocks, (select count(distinct dbms_rowid.rowid_block_number(rowid)) from t) from user_tables where table_name = 'T';


    BLOCKS (SELECTCOUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))FROMT)
---------- ----------------------------------------------------------------
       433                                                                0



And if you use ASSM (automatic segment space management), you'll typically find more than one block initially under the high water mark for free space management.  We use blocks in your table to manage space for your table.

 

ops$tkyte@ORA10GR2> select tablespace_name, segment_space_management from dba_tablespaces;
 
TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL
SYSAUX                         AUTO
USERS                          AUTO
UNDOTBS2                       MANUAL
EXAMPLE                        AUTO
BIG_TABLE                      AUTO
TEMPSPC                        MANUAL
X_8M                           MANUAL
Y_48K                          MANUAL
MSSM                           AUTO
 
10 rows selected.