Thanks for the question, Prasad.

Asked: May 18, 2016 - 8:29 am UTC

Last updated: September 05, 2017 - 2:12 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,
I'm using Oracle 11g Enterprise edition database. I've an existing table with both Range partitions and hash sub-partitions. Currently the number of sub-partitions are set as 64 when the table was first created and I may have a requirement in near future to increase the number to a higher value (probably to 128). Here's part of my table definition where the partitioning information is specified:

PARTITION BY RANGE (RECORD_INS_DATE)
 INTERVAL (Numtodsinterval(1,'day')) 
 SUBPARTITION BY HASH(BUSINESS_ID, SOURCE_SYSTEM, DESTINATION_SYSTEM)
     SUBPARTITIONS 64  
 (PARTITION P_FIRST_PART VALUES LESS THAN (TO_DATE('16-DEC-2015','DD-MON-YYYY')))

2 Questions:

1) Is it possible to NOT specify the number of sub-partitions at all and let oracle create sub-partitions dynamically as and when it encounters a new hash value?

2) If #1 is not possible, may I ask if I can increase the value from 64 to 128 please without dropping the existing table as the table contains data? If so, what command do I need to use?

Thanks in advance.

Kind Regards,
PP

and Chris said...

1. The number of partitions you specify determines how many hash values there are. If you say 64 hash partitions, there can only be 64 hash values. Oracle can't encounter a 65th value.

So no.

2. You can change the subpartition template. All new partitions will then have that many hash subpartitions. This only affects new partitions. Existing partitions will still have the same number of subpartitions:

SQL> create table t (
  2    record_ins_date    date, business_id int,
  3    source_system      int, destination_system int
  4  ) partition by range ( record_ins_date )
  5    interval ( numtodsinterval ( 1,'day' ) )
  6    subpartition by hash (
  7      business_id, source_system, destination_system
  8    ) subpartitions 64 (
  9      partition p_first_part values less than (
 10        to_date ( '16-DEC-2015','DD-MON-YYYY' )
 11      )
 12    ) ;

Table created.

SQL>
SQL> select count(*) from user_tab_subpartitions
  2  where  table_name = 'T';

  COUNT(*)
----------
        64

SQL>
SQL> alter table t set subpartition template 128;

Table altered.

SQL>
SQL> /* Existing partition unchanged */
SQL> select count(*) from user_tab_subpartitions
  2  where  table_name = 'T';

  COUNT(*)
----------
        64

SQL>
SQL> insert into t values (sysdate, 1, 1, 1);

1 row created.

SQL>
SQL> /* 128 subpartitons for new partition (192-64 = 128)*/
SQL> select count(*) from user_tab_subpartitions
  2  where  table_name = 'T';

  COUNT(*)
----------
       192

If you want to add more subpartitions to your existing partitions, you'll need to add them explicitly:

SQL> alter table t modify partition p_first_part add subpartition p65;

Table altered.

SQL> alter table t modify partition p_first_part add subpartition p66;

Table altered.

SQL>
SQL> select count(*) from user_tab_subpartitions
  2  where  table_name = 'T';

  COUNT(*)
----------
       194

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you for your quick and accurate response!

Prasad Panchagnula, May 18, 2016 - 9:30 am UTC

Hi Chris,
My sincere thanks for your quick and detailed response. It also contains a solid example to explain it. I can't ask for any more. Wonderful!

Kind Regards,
Prasad Panchagnula

Question

A reader, May 18, 2016 - 2:46 pm UTC

Although this is very good answer just asking if newly hashed cols match with the old ones.
Since this apply to forward partions could happen to have same col value with different hash results old subpartition vs New one?
If so I think recreating the table is required for consistency (why not a dbms redefinition )

May 18, 2016 - 4:40 pm UTC

Well you have more partitions, so at least some of the values must go to a different partition. Otherwise half would be empty!

When you add subpartitions to existing partitions with data, Oracle will move the rows automatically:

create table t (
  record_ins_date    date, business_id int,
  source_system      int, destination_system int
) partition by range ( record_ins_date )
  interval ( numtodsinterval ( 1,'day' ) )
  subpartition by hash (
    business_id, source_system, destination_system
  ) subpartitions 4 (
    partition p_first_part values less than (
      to_date ( '16-DEC-2015','DD-MON-YYYY' )
    )
  ) ;
  
insert into t
  select sysdate, rownum, rownum, rownum from dual
  connect by level <= 64;
  
select partition_name from user_tab_partitions
where  table_name = 'T';

PARTITION_NAME               
------------------------------
P_FIRST_PART                  
SYS_P12169  
  
commit;

SQL> select uo.subobject_name, count(*)
  2  from   t, user_objects uo
  3  where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
  4  group  by uo.subobject_name
  5  order  by 1;

SUBOBJECT_NAME COUNT(*)
-------------- --------
SYS_SUBP12165        13
SYS_SUBP12166        21
SYS_SUBP12167        15
SYS_SUBP12168        15

SQL>
SQL> alter table t modify partition SYS_P12169 add subpartition p5;

Table altered.

SQL> alter table t modify partition SYS_P12169 add subpartition p6;

Table altered.

SQL> alter table t modify partition SYS_P12169 add subpartition p7;

Table altered.

SQL> alter table t modify partition SYS_P12169 add subpartition p8;

Table altered.

SQL>
SQL> select uo.subobject_name, count(*)
  2  from   t, user_objects uo
  3  where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
  4  group  by uo.subobject_name
  5  order  by 1;

SUBOBJECT_NAME COUNT(*)
-------------- --------
P5                    6
P6                   12
P7                    8
P8                    9
SYS_SUBP12165         7
SYS_SUBP12166         9
SYS_SUBP12167         7
SYS_SUBP12168         6

8 rows selected.

Bravo

A reader, May 18, 2016 - 5:28 pm UTC

Tkx for loving what you are doing.

So the enable row movement for hash subpartition is implicit?

May 19, 2016 - 1:40 am UTC

No its not...


SQL>
SQL>  create table t (
  2        record_ins_date    date,
  3        business_id int,
  4        source_system      int,
  5        destination_system int
  6      ) partition by range ( record_ins_date )
  7        interval ( numtodsinterval ( 1,'day' ) )
  8        subpartition by hash (
  9          business_id, source_system, destination_system
 10        ) subpartitions 12 (
 11          partition p_first_part values less than (
 12           to_date ( '16-DEC-2015','DD-MON-YYYY' )
 13         )
 14       ) ;

Table created.

SQL>
SQL> insert into t
  2  select sysdate, rownum, rownum, rownum
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> select uo.subobject_name, count(*)
  2      from   t, user_objects uo
  3      where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
  4      group  by uo.subobject_name
  5      order  by 1;

SUBOBJECT_NAME                   COUNT(*)
------------------------------ ----------
SYS_SUBP13356                           4
SYS_SUBP13357                           5
SYS_SUBP13358                           6
SYS_SUBP13359                           5
SYS_SUBP13360                           9
SYS_SUBP13361                          17
SYS_SUBP13362                          13
SYS_SUBP13363                          13
SYS_SUBP13364                           7
SYS_SUBP13365                           5
SYS_SUBP13366                           6
SYS_SUBP13367                          10

12 rows selected.

SQL>
SQL> update t
  2  set source_system = source_system + 1, destination_system = destination_system + 1;
update t
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>
SQL>

Cheers,
Connor

Add many hash subpartitions at once

Oleg Samarin, September 04, 2017 - 7:40 am UTC

Because adding of each hash subpartition requires rehashing all data in all existing subpartitions, may I add 64 subpartitions at once?

September 04, 2017 - 2:30 pm UTC

I'm not aware of a way to do this. But when you add hash (sub)partitions, only moves rows from one of the existing partitions:

create table t (
  record_ins_date    date, business_id int,
  source_system      int, destination_system int
) partition by range ( record_ins_date )
  subpartition by hash (
    business_id, source_system, destination_system
  ) subpartitions 2 (
    partition p_first_part values less than (
      to_date ( '05-SEP-2017','DD-MON-YYYY' )
    )
  ) ;
  
insert into t
  select sysdate, rownum, rownum, rownum from dual
  connect by level <= 1024;
  
commit;
  
select uo.object_name, uo.subobject_name, count(*)
from   t, user_objects uo
where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
group  by uo.object_name, uo.subobject_name
order  by 1;

OBJECT_NAME  SUBOBJECT_NAME  COUNT(*)  
T            SYS_SUBP18241   504       
T            SYS_SUBP18242   520 

alter table t modify partition p_first_part add subpartition;

select uo.subobject_name, count(*)
from   t, user_objects uo
where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
group  by uo.subobject_name
order  by 1;

SUBOBJECT_NAME  COUNT(*)  
SYS_SUBP18241   237       
SYS_SUBP18242   520       
SYS_SUBP18243   267 

alter table t modify partition p_first_part add subpartition;

select uo.subobject_name, count(*)
from   t, user_objects uo
where  dbms_rowid.rowid_object(t.rowid) = uo.data_object_id
group  by uo.subobject_name
order  by 1;

SUBOBJECT_NAME  COUNT(*)  
SYS_SUBP18241   237       
SYS_SUBP18242   272       
SYS_SUBP18243   267       
SYS_SUBP18244   248

This makes the process relatively efficient. You're only ever moving ~half the rows out of one partition.

Read more about this from Jonathan Lewis at:

https://jonathanlewis.wordpress.com/2009/09/21/hash-partitions-2/#more-2242

adding multiple subpartitions in a single command

Rajeshwaran, Jeyabal, September 05, 2017 - 1:14 pm UTC

Team:

I have an existing Composite partition table, something like this ( this demo is from Oracle 12cR2 database ).

demo@ORA12C> create table t
  2  partition by range(x)
  3  subpartition by hash(object_id)
  4  subpartition template(
  5     subpartition sp1,
  6     subpartition sp2 )
  7  (  partition p_2014 values less than (to_date('01-Jan-2015','dd-mon-yyyy')) )
  8  as
  9  select a.*, to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,200) x
 10  from all_objects a;

Table created.

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',granularity=>'ALL');

PL/SQL procedure successfully completed.

demo@ORA12C> select table_name,partition_name,subpartition_name,num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first,
  5          subpartition_name ;

TABLE_NAME PARTITION_ SUBPARTITION_NA   NUM_ROWS
---------- ---------- --------------- ----------
T                                          61447
T          P_2014     P_2014_SP1           30837
T          P_2014     P_2014_SP2           30610
T          P_2014                          61447

we are planning to add multiple subpartitions in a single command like this.

demo@ORA12C> alter table t modify partition p_2014
  2  add subpartition sp3,
  3     subpartition sp4;

Table altered.

but we see only one subpartition got added, not all.

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T',granularity=>'ALL');

PL/SQL procedure successfully completed.

demo@ORA12C> select table_name,partition_name,subpartition_name,num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first,
  5          subpartition_name ;

TABLE_NAME PARTITION_ SUBPARTITION_NA   NUM_ROWS
---------- ---------- --------------- ----------
T                                          61447
T          P_2014     P_2014_SP1           15390
T          P_2014     P_2014_SP2           30610
T          P_2014     SP4                  15447
T          P_2014                          61447

demo@ORA12C>

Please advise.

September 05, 2017 - 2:12 pm UTC

Like I said, I'm not aware of a way to do this. AFAIK you can only add one hash (sub)partition at a time.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you for your quick and accurate response!

Question

Bravo

Add many hash subpartitions at once

adding multiple subpartitions in a single command

More to Explore

VLDB

VLDB

Increasing the number of hash sub-partitions

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you for your quick and accurate response!

Question

Bravo

Add many hash subpartitions at once

adding multiple subpartitions in a single command

More to Explore

VLDB

VLDB