Skip to Main Content
  • Questions

  • Adding hash partitions and spreading data across

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Francesco.

Asked: February 20, 2018 - 3:27 pm UTC

Last updated: February 21, 2018 - 3:25 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I have a table with a certain number of range partitions and for each partitions I have eight hash subpartitions. Is there a way to increase the subpartitions number to ten and distributing evenly the number of rows?
I have tried "alter table...add subpartition" but the number of rows distributes only between two of them. I have also searched the web but have not been able to find an answer.

Could you please help me?

Thank you very much in advance.

Francesco

and Chris said...

That's the way hash partitioning works. The rows in each partition will only be (roughly) even if the number of partitions is a power of 2:

create table t (x) partition by hash (x) partitions 2 
as 
  select level from dual
  connect by level <= 100;
  
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;

SUBOBJECT_NAME   COUNT(*)   
SYS_P12236               55 
SYS_P12235               45 

alter table t add partition ;

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;

SUBOBJECT_NAME   COUNT(*)   
SYS_P12236               55 
SYS_P12237               25 
SYS_P12235               20 

alter table t add partition ;

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;

SUBOBJECT_NAME   COUNT(*)   
SYS_P12236               28 
SYS_P12237               25 
SYS_P12238               27 
SYS_P12235               20


It's the same principle for subpartitions.

Rating

  (1 rating)

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

Comments

Just looking for...

Francesco Dell'Orco, February 21, 2018 - 2:47 pm UTC

Hi Chris and thank you for your fast response.

I was already aware we could not add partitions (and subpartitions as well) and spread rows evenly unless the initial number was a power of 2. Unfortunately this is not the best for our goals at the moment.

However I'm looking for a way to do it and I am guessing anyone over the community has had such an issue like this one.

Kind regards,

Francesco


Chris Saxon
February 21, 2018 - 3:25 pm UTC

It's not possible. The hash mechanism is controlled internally. You'll need to look at another partitioning method.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.