22 May, 2016

TRUNCATEing a Table makes an UNUSABLE Index VALID again

Here's something I learned from Jonathan Lewis sometime ago.

If you set an Index to be UNUSABLE but later issue a TRUNCATE TABLE, the Index becomes VALID again --- i.e. the Index gets updated with rows subsequently inserted.

SQL> connect hemant/hemant
Connected.
SQL> drop table target_data purge;

Table dropped.

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> create index target_data_ndx_1      
  2  on target_data(owner, object_type, object_name);

Index created.

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data; 

367156 rows created.

SQL> commit;

Commit complete.

SQL> col segment_name format a30
SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                         49
TARGET_DATA_NDX_1              INDEX                         19

SQL> 
SQL> col index_name format a30
SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              VALID

SQL> 


So, I have a VALID Index on my Table.

I now make it UNUSABLE and add rows to it.

SQL> alter index target_Data_ndx_1 unusable;

Index altered.

SQL> select status     
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_1';

STATUS
--------
UNUSABLE

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              UNUSABLE

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                        104

SQL> 


Oracle actually drops the Index segment (so you don't see it in USER_SEGMENTS) when it is set to UNUSABLE alhough the Index definition is still present.  The Index doesn't "grow" as the Segment doesn't exist.

Let me TRUNCATE the table.

SQL> truncate table target_data;

Table truncated.

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                      .0625
TARGET_DATA_NDX_1              INDEX                      .0625

SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'TARGET_DATA';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_DATA_NDX_1              VALID

SQL> 


Immediately after the TRUNCATE TABLE, the Index Segment is instantiated and the Index becomes VALID again.  So inserting rows will update the Index.  My last explicit command against the Index was ALTER INDEX ... UNUSABLE but that seems to be not the current state now !

SQL> insert /*+ APPEND */ into target_data
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name like 'TARGET_DATA%'    
  4  order by 1;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA                    TABLE                        49
TARGET_DATA_NDX_1              INDEX                        19

SQL> 


So, repopulating the Table has expanded the Index again.
.
.
.


18 May, 2016

Partition Storage -- 8 : Manually Sizing Partitions

As demonstrated in the first post in this series, the  default size for Table Partitions is to start with an I nitial8MB Extent.  The fourth post demonstrated how to resize a Partition that has already been created and populated.

Here is a method to pre-size the Initial Extent. I start with 3 partitions of different sizes.

SQL> create table my_part_tbl_init_sized (id_column number(6), data_column varchar2(100))
  2  partition by range (id_column)
  3  (partition p_100 values less than (101) segment creation immediate                                
  4                                          storage (initial 64K next 64K) tablespace hemant,
  5   partition p_200 values less than (201) segment creation immediate
  6                                          storage (initial 1M next 1M) tablespace hemant,
  7   partition p_max values less than (maxvalue) segment creation immediate
  8                                          storage (initial 8M next 1M) tablespace hemant)
  9  /

Table created.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_MAX               8388608     1048576 HEMANT

SQL> 
SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100                64          1
P_200               1024         1
P_MAX               8192         1

SQL> 


However, the caveat about Partition Extent Sizes when SPLIT is used as shown in the third post still applies.

SQL> alter table my_part_tbl_init_sized
  2  split partition p_max
  3  at (301)
  4  into (partition p_300, partition p_max)
  5  /

Table altered.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_300               8388608     1048576 HEMANT
P_MAX               8388608     1048576 HEMANT

SQL> 
SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100                64          1
P_200              1024          1
P_300              8192          1
P_MAX              8192          1

SQL> 


So, SPLITting P_MAX to create P_300 created P_300 with an 8MB extent because the sizing is inherited from P_MAX.  How do I change the Partition Extent sizes on SPLIT ?

SQL> alter table my_part_tbl_init_sized
  2  split partition p_max
  3  at (501)
  4  into (partition p_500 storage (initial 64K next 64K) tablespace hemant, partition p_max)
  5  /

Table altered.

SQL> 
SQL> alter table my_part_tbl_init_sized
  2  split partition p_500
  3  at (401)
  4  into (partition p_400, partition p_500)
  5  /

Table altered.

SQL> 
SQL> select partition_name, initial_extent, next_extent, tablespace_name
  2  from user_tab_partitions
  3  where table_name = upper('my_part_tbl_init_sized')
  4  order by partition_position
  5  /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100                 65536       65536 HEMANT
P_200               1048576     1048576 HEMANT
P_300               8388608     1048576 HEMANT
P_400                 65536       65536 HEMANT
P_500                 65536       65536 HEMANT
P_MAX               8388608     1048576 HEMANT

6 rows selected.

SQL> select partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name = upper('my_part_tbl_init_sized')
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1
  6  /

PARTITION_NA BYTES/1024    EXTENTS
------------ ---------- ----------
P_100               64           1
P_200             1024           1
P_300             8192           1
P_400               64           1
P_500               64           1
P_MAX             8192           1

6 rows selected.

SQL> 


This time, when I split P_MAX I deliberately specified a size for P_500.  Then, when I split P_500 further, the new P_400 inherited the sizing from P_500.
,
,
,

09 May, 2016

Compression -- 7 : Updating after BASIC Compression

In the first blog post on compression, I had shown BASIC Compression and also the effect of executing an UPDATE on a table with BASIC compression.

To expand on the them of UPDATEs of BASIC compression blocks ....

SQL> select count(*) from source_data;

  COUNT(*)
----------
    367156

SQL> create table target_comp row store compress basic as select * from source_data where 1=2;

Table created.

SQL> select pct_free from user_tables where table_name = 'TARGET_COMP';

  PCT_FREE
----------
         0

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

 CHAIN_CNT     BLOCKS
---------- ----------
         0       4452

SQL> 
SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

  NUM_ROWS  CHAIN_CNT     BLOCKS
---------- ---------- ----------
   1101468          0       4452

SQL> 


So we have a table with 1.1million rows and no Row Chaining.

What happens if we update about 20% of the rows ?

SQL> begin
  2  for rec in (select rowid from target_comp where rownum < 220001)
  3  loop
  4    update target_comp set owner=owner where rowid=rec.rowid;
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

 CHAIN_CNT     BLOCKS
---------- ----------
    202189       7882

SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

  NUM_ROWS  CHAIN_CNT     BLOCKS
---------- ---------- ----------
   1101468     202189       7882

SQL> 


I have updated 220,000 rows without actually increasing the notional length of each row (I set OWNER=OWNER).  Yet, The CHAIN_CNT is now 202K and the table's HighWaterMark has expanded from 4,452 blocks to 7,882 blocks.  A significant increase !
(YMMV may vary in your tests !)

It was Jonathan Lewis who suggested getting the Chain Count (or LIST CHAINED ROWS) to understand the impact of UPDATEs on a table with BASIC compression.
.
.
.


05 May, 2016

Compression -- 6b : Advanced Index Compression (revisited)

Following up on my earlier post on 12.1.0.2 Advanced Index Compression, one of my readers asked what would be the difference if I reversed the order of columns in the chosen index.

My defined index was on (OWNER, OBJECT_TYPE, OBJECT_NAME) --- defined as being from the column with the fewest distinct values to the most.  This ordering is best compressible with Index Key Compression (also known as Prefix Compression).  If I reverse the order, Index Key Compression for the two leading columns wouldn't deliver the same level of compression.  The question is whether Advanced Index Compression can intelligently handle the reversal.

SQL> create index target_data_ndx_3_comp on
  2  target_data(object_name, object_type, owner) compress 2;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_3_COMP'
  4  /

LEAF_BLOCKS
-----------
       3091

SQL> 


Surprisingly, this index with Prefix 2 on (OBJECT_NAME, OBJECT_TYPE) is, at 3,091 leaf blocks, smaller than the previous  index with Prefix 2 on (OWNER, OBJECT_TYPE) at 5,508 leaf blocks.

Continuing with Prefix 3

SQL> drop index target_data_ndx_3_comp;

Index dropped.

SQL> create index target_data_ndx_3_comp on
  2  target_data(object_name, object_type, owner) compress 3;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_3_COMP'
  4  /

LEAF_BLOCKS
-----------
       2277

SQL> 


At 2,277 leaf blocks it is, as expected, the same size with Prefix 3 on (OWNER, OBJECT_TYPE, OBJECT_NAME).  Since the entire index key is specified as the Prefix, both indexes would be the same size.

Going on to Advanced Index Compression

SQL> drop index target_data_ndx_3_comp;

Index dropped.

SQL> create index target_data_ndx_4_advcomp on
  2  target_data(object_name, object_type, owner)
  3  compress advanced low
  4  /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_4_ADVCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_4_ADVCOMP'
  4  /

LEAF_BLOCKS
-----------
       2277

SQL> 


This is, again, as expected.  Advanced Index Compression results in the same size irrespective of the ordering of the columns.

The advantage of Advanced Index Compression over Key or Prefix Compression is that the DBA does not need to determine the Prefix for compression.  He does not have to spend time to analyze the data and compare the number of distinct values for each of the columns in the composite index.
.
.
.

03 May, 2016

Compression -- 6 : Advanced Index Compression

Earlier, I had covered Index (Key) Compression which is included in the Enterprise Edition.

In Key Compression, the DBA must specify the Prefix (i.e. number of leading columns in a composite index) that must be used as the compression key.

12.1.0.2 Advanced Index Compression does not require the DBA to manually identify the prefix key length.  Advanced Index Compression auto(magically) identifies the optimal prefix columns.
(Note : Advanced Index Compression requires the Advanced Compression licence Option and is available only in 12.1.0.2 and higher)

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> 


Creating a Key Compression Index by specifying the Prefix size (the leading 2 columns) :

SQL> create index target_data_ndx_1_comp on
  2  target_data (owner, object_type, object_name)  compress 2;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_1_COMP'
  4  /

LEAF_BLOCKS
-----------
       5508

SQL> 


Note how I specified "2" as the Prefix size as I want to compress on repeated values of (OWNER, OBJECT_NAME).

Using Advanced Index Compression by specifying "COMPRESS ADVANCED LOW" and letting Oracle decide on the compression strategy in each leaf block :

SQL> drop index target_data_ndx_1_comp;

Index dropped.

SQL> create index target_data_ndx_2_advcomp on
  2  target_data (owner, object_type, object_name)
  3  compress advanced low;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_ADVCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_2_ADVCOMP'
  4  /

LEAF_BLOCKS
-----------
       2277

SQL> 


Wow, that's significantly smaller.  What's more, I did not have to spend time analyzing the data and the index definition to identify the "correct" Prefix size.

However, it is now possible to specify the entire composite key as the Prefix, although that is not what I would have done in earlier versions.  Identifying the Prefix size requires analyzing the data.

SQL> create index target_data_ndx_1_comp on
  2  target_data (owner, object_type, object_name)  compress 3
  3  /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_1_COMP'
  4  /

LEAF_BLOCKS
-----------
       2277

SQL> 


So, the Advanced option allows me to let Oracle automatically decide the appropriate mechanism to compress the Index keys.

UPDATE : Also see the subsequent test with a reversal of the columns in the composite index.

Just for comparison, here is a regular index :

SQL> drop index target_data_ndx_2_advcomp;

Index dropped.

SQL> create index target_data_ndx_3_nocomp on
  2  target_data (owner, object_type, object_name)
  3  /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_NOCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_3_NOCOMP'
  4  /

LEAF_BLOCKS
-----------
       7289

SQL> 


That is a much larger regular index !
.
.
.

02 May, 2016

FBDA -- 7 : Maintaining Partitioned Source Table

Taking up the TEST_FBDA_PARTITIONED table,  let's look at a couple of Partition Maintenance operations.

SQL> select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE                  NUM_ROWS
---------------- ------------------------- ----------
P_100            101                              100
P_200            201                              100
P_300            301                              100
P_400            401                              100
P_MAX            MAXVALUE                         301

SQL> 


Let's try a TRUNCATE PARTITION

SQL> alter table test_fbda_partitioned truncate partition p_100;

Table truncated.

SQL> 


So, that's supported.

Let's try a SPLIT PARTTIION

SQL> alter table test_fbda_partitioned       
  2  split partition p_max at (501)
  3  into (partition p_500, partition p_max)
  4  /
alter table test_fbda_partitioned
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> 


So, a SPLIT PARTITION fails.  We need to DISASSOCIATE the Flashback Archive.

SQL> execute dbms_flashback_archive.disassociate_fba('HEMANT','TEST_FBDA_PARTITIONED');

PL/SQL procedure successfully completed.

SQL> select table_name, flashback_archive_name, archive_table_name, status
  2  from user_flashback_archive_tables
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME          STATUS
----------------------------------------------------- -------------
TEST_FBDA_PARTITIONED
FBDA
SYS_FBA_HIST_93342          DISASSOCIATED


SQL> 
SQL> alter table test_fbda_partitioned
  2  split partition p_max at (501)
  3  into (partition p_500, partition p_max)
  4  /

Table altered.

SQL> execute dbms_flashback_archive.reassociate_fba('HEMANT','TEST_FBDA_PARTITIONED');

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name, flashback_archive_name, archive_table_name, status
  2  from user_flashback_archive_tables
  3  where table_name = 'TEST_FBDA_PARTITIONED'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME          STATUS
----------------------------------------------------- -------------
TEST_FBDA_PARTITIONED
FBDA
SYS_FBA_HIST_93342          ENABLED


SQL> 


While a Table is disassociated with it's Flashback Archive, DDL that would not normally be permitted may be done under strict control to ensure that there is no data divergence.

.
.
.

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