Partition Storage -- 5 : Partitioned Table versus Non-Partitioned Table ? (in 12.1)

Reviewing my second blog post in this series, I found it strange that Partition P_100 (populated by Serial Inserts of 1 row, 100,000 rows, 500,000 rows and 500,000 rows) had such a High Water Mark.

For 1.1million rows of an Average Row Length of 11, the High Water Mark was 3,022 blocks.

In the fourth blog post, a simple ALTER TABLE MOVE PARTITION had brought the High Water Mark to 2,482 blocks !

This needs further investigation.

Let's compare a single Partition of a Partitioned Table with a Non-Partitioned Table for exactly the same data and same pattern of INSERT statements.

Starting with a new Partitioned Table.

SQL> l
  1  create table new_part_tbl (id_column number(6), data_column varchar2(100))
  2  partition by range (id_column)
  3  (partition p_100 values less than (101),
  4  partition p_200 values less than (201),
  5  partition p_300 values less than (301),
  6  partition p_400 values less than (401),
  7* partition p_max values less than (maxvalue))
SQL> /

Table created.

SQL>
SQL> insert into new_part_tbl values (51,'Fifty One');

1 row created.

SQL> 
SQL> insert into new_part_tbl
  2  select 25, 'New Row'
  3  from dual
  4  connect by level < 100001
  5  /

100000 rows created.

SQL> insert into new_part_tbl
  2  select 45, 'New Row'
  3  from dual
  4  connect by level < 500001
  5  /

500000 rows created.

SQL> /

500000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
  2  from user_tab_partitions
  3  where table_name = 'NEW_PART_TBL'
  4  and partition_name = 'P_100'
  5  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       3022

SQL> 
SQL> REM  Let's MOVE the Partition
SQL> alter table new_part_tbl move partition P_100;

Table altered.

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

PL/SQL procedure successfully completed.

SQL> 
SQL> select avg_row_len, num_rows, blocks
  2  from user_tab_partitions
  3  where table_name = 'NEW_PART_TBL'
  4  and partition_name = 'P_100'
  5  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2484

SQL> 
SQL> l
  1  select extent_id, blocks
  2  from dba_extents
  3  where segment_name = 'NEW_PART_TBL'
  4  and segment_type = 'TABLE PARTITION'
  5  and partition_name = 'P_100'
  6  and owner = 'HEMANT'
  7* order by 1
SQL> /

 EXTENT_ID     BLOCKS
---------- ----------
        0        1024
        1        1024
        2        1024

SQL> 

As expected (see the first blog post), the Extents are still 8MB each.  But the High Water Mark has "magicallly" shrunk from 3,022 blocks to 2,484 blocks.

Let's create a Non-Partitioned Table with the same columns and rows.

SQL> create table non_part_tbl (id_column number(6), data_column varchar2(100));

Table created.

SQL> insert into non_part_tbl values (51,'Fifty One');

1 row created.

SQL> insert into non_part_tbl
  2  select 25, 'New Row'
  3  from dual
  4  connect by level < 100001
  5  /

100000 rows created.

SQL> insert into non_part_tbl
  2  select 45, 'New Row'
  3  from dual
  4  connect by level < 500001
  5  /

500000 rows created.

SQL> /

500000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
  2  from user_tables
  3  where table_name = 'NON_PART_TBL'
  4  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2512

SQL> 
SQL> REM Let's MOVE the Table
SQL> alter table non_part_tbl move;

Table altered.

SQL> select avg_row_len, num_rows, blocks
  2  from user_tables
  3  where table_name = 'NON_PART_TBL'
  4  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2512

SQL> 
SQL> l
  1  select extent_id, blocks
  2  from dba_extents
  3  where segment_name = 'NON_PART_TBL'
  4  and segment_type = 'TABLE'
  5  and owner = 'HEMANT'
  6* order by 1
SQL> /

 EXTENT_ID     BLOCKS
---------- ----------
         0          8
         1          8
         2          8
         3          8
         4          8
         5          8
         6          8
         7          8
         8          8
         9          8
        10          8
        11          8
        12          8
        13          8
        14          8
        15          8
        16        128
        17        128
        18        128
        19        128
        20        128
        21        128
        22        128
        23        128
        24        128
        25        128
        26        128
        27        128
        28        128
        29        128
        30        128
        31        128
        32        128
        33        128
        34        128

35 rows selected.

SQL> 

The Non-Partitioned Table had a High Water Mark of 2,512 blocks.  This did not change with a MOVE.  The allocation of Extents is also expected in AutoAllocate.

Why, then, does the Partition behave differently ?  It started with a High Water Mark of 3,022 blocks which shrunk to 2,484 blocks after a MOVE ?

Is the Average Row Length or the actual data a factor ?  (Note : I am *not* using Table Compression).

To be explored further with a larger row size ...........

Possibly, to be explored with a different pattern of INSERT statements  ......

Possibly to be compared in 11.2 as well. ......
.
.
.