Partition Storage -- 7 : Revisiting HWM - 2 (again)

Revisiting the previous test case, but with a larger AVG_ROW_LEN

SQL> create table part_table_large
(id_column number(6), data_col_1 varchar2(100), data_col_2 varchar2(100))
  partition by range (id_column)
  (partition p_100 values less than (101),
   partition p_200 values less than (201),
   partition p_300 values less than (301),
   partition p_400 values less than (401),
   partition p_max values less than (maxvalue))
/
  2    3    4    5    6    7    8    9  

Table created.

SQL> 
SQL> insert into part_table_large values 
(51,rpad('String',75,'X'), rpad('Another',60,'Y'))
  2    3  
SQL> /

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 100000
    loop
     insert into part_table_large 
values (25, rpad('String',75,'X'), rpad('Another',60,'Y')); 
     commit;
     cntr := cntr + 1;
    end loop;
end;
  2    3    4    5    6    7    8    9   10   11   12   13  
 14  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_large 
values (45, rpad('String',75,'X'), rpad('Another',60,'Y')); 
     commit;
     cntr := cntr + 1;
    end loop;
end;
  2    3    4    5    6    7    8    9   10   11   12   13  
 14  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_large 
values (55, rpad('String',75,'X'), rpad('Another',60,'Y')); 
     commit;
     cntr := cntr + 1;
    end loop;
end;
  2    3    4    5    6    7    8    9   10   11   12   13  
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats('','PART_TABLE_LARGE',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> 
SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_LARGE'
and partition_name = 'P_100'
/
  2    3    4    5  
AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
 140           1100003      22349

SQL> 
SQL> 
SQL> alter table part_table_large move partition p_100 ;

Table altered.

SQL> 
SQL> exec dbms_stats.gather_table_stats('','PART_TABLE_LARGE',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> 
SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_LARGE'
and partition_name = 'P_100'
/
  2    3    4    5  
AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
 140           1100003      22626

SQL> 
SQL> 
SQL> select extent_id, blocks
from dba_extents
where segment_name = 'PART_TABLE_LARGE'
and segment_type = 'TABLE PARTITION'
and partition_name = 'P_100'
and owner = 'HEMANT'
order by 1
/
  2    3    4    5    6    7    8  
 EXTENT_ID     BLOCKS
---------- ----------
  0              1024
  1              1024
  2              1024
  3              1024
  4              1024
  5              1024
  6              1024
  7              1024
  8              1024
  9              1024
 10              1024
 11               512
 12              1024
 13              1024
 14              1024
 15              1024
 16              1024
 17              1024
 18              1024
 19              1024
 20              1024
 21              1024
 22              1024

23 rows selected.

SQL> 

Aha ! Unlike the previous case (where an AVG_ROW_LEN of 11, a MOVE reduced the HWM from 3,022 to 2,484), with a larger row size, the HWM has moved from 22,349 to 22,626.

So, space consumption is a factor of both the AVG_ROW_LEN and the manner in which the rows are  inserted / relocated.

SQL> l
  1  select avg_row_len*num_rows*1.2/8192 Expected_Blocks, Blocks
  2  from user_tab_partitions
  3  where table_name = 'PART_TABLE_LARGE'
  4* and partition_name = 'P_100'
SQL> /

EXPECTED_BLOCKS     BLOCKS
--------------- ----------
     22558.6553      22626

SQL> 

Also, see how the "Expected Blocks" count seems more accurate than earlier.
.
.
.