cancel
Showing results for 
Search instead for 
Did you mean: 

ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

SOLVED
Highlighted
Gold Adventurer

ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

Hi all,reading  the documentation of PPAS 9.6 said  : 

 

...New partitions must be of the same type (LIST, RANGE or HASH) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partition(s)...

 

https://www.enterprisedb.com/docs/en/9.6/DB_Compat_Oracle_Developers_Guide/Database_Compatibility_fo...

 

 

 

when i execute the command add partition on table with hash partition , i got error: 

 

CREATE TABLE test_hash2
(
nom number,
md5_char varchar2

)
PARTITION BY HASH (nom)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);

ALTER TABLE test_hash2 ADD PARTITION p4;

ERROR:  cannot add a hash partition
********** Error **********

ERROR: cannot add a hash partition
SQL state: 0A000

the documentation is bad? or i am missing something? or i dont understand the documentation? 

in the syntax said (only list and range) :

 

Use the ALTER TABLE… ADD PARTITION command to add a partition to an existing partitioned table. The syntax is:
ALTER TABLE table_name ADD PARTITION partition_definition;
Where partition_definition is:
{list_partition | range_partition }

 i dont see hash

 

 

Regards

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Re: ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

Hi Asotolongo,

It seems that the partition name which you are trying to provide is not correct.

Try providing the target partition name as "p1",

enterprisedb@edb  ALTER TABLE test_hash
EXCHANGE PARTITION p1
WITH TABLE enterprisedb.change;
ALTER TABLE


For exact partition name and details you can check the table "all_tab_partitions".

View solution in original post

4 REPLIES 4
Moderator

Re: ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

Hi Asotolongo,

Hope you are doing good !

Adding hash partitions isn't supported yet. As mentioned in document the only supported syntax for the ADD PARTITION command to add a partition to an existing partitioned table is for the LIST and RANGE partition.

Use the ALTER TABLE… ADD PARTITION command to add a partition to an existing partitioned table. The syntax is:
ALTER TABLE table_name ADD PARTITION partition_definition;
Where partition_definition is:
{list_partition | range_partition }


Adding a hash partition would require redistributing data between the existing partitions.  For example, if there are 3 partitions, all of the rows where the hash value of the partition key is a multiple of 3 go into partition 0; those where the hash value of the partition key is 1 more than a multiple of 3 go into partition 1; those where the hash value of the partition key is 2 more than a multiple of 3 go into partition 2.  When you switch to 4 partitions, most rows must be moved, because the partition assignments are now based on multiples of 4 rather than multiples of 3.  Advanced Server does not currently have a command that performs automatic row movement, so there is no single command that can add a partition.

The below line in the document seems to be documentation bug, i will check this internally to fix this.

New partitions must be of the same type (LIST, RANGE or HASH) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partition(s).
Gold Adventurer

Re: ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

hi again Ranjeet, thanks for your explanations ,please check de documentation in EXCHANGE PARTITION too because 

https://www.enterprisedb.com/docs/en/9.6/DB_Compat_Oracle_Developers_Guide/Database_Compatibility_fo...

 

said: 

..."The ALTER TABLE… EXCHANGE PARTITION command can exchange partitions in a LIST, RANGE or HASH partitioned table. The structure of the source_table must match the structure of the target_table (both tables must have matching columns and data types), and the data contained within the table must adhere to the partitioning constraints."...

and the hash does not work either

when i try the following:

 

CREATE TABLE test_hash
(
nom integer,
md5_char varchar2

)
PARTITION BY HASH (nom)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);

insert into test_hash 
select i, md5(i::text) from generate_series(1,10000) as i ;


SELECT  tableoid::regclass,count (tableoid)  FROM test_hash group by 1;
"test_hash_p1",3249
"test_hash_p2",3361
"test_hash_p3",3390
--create the exchange table CREATE TABLE change (nom integer,md5_char varchar2); insert into change select * from test_hash_p1 ALTER TABLE test_hash EXCHANGE PARTITION test_hash_p1 WITH TABLE public.change;

i got the error:

 

ERROR: partition or subpartition "test_hash_p1" not found

********** Error **********

ERROR: partition or subpartition "test_hash_p1" not found
SQL state: 42P17

the error seems unlikely.

 

 

regards

 

 

 

Moderator

Re: ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

Hi Asotolongo,

It seems that the partition name which you are trying to provide is not correct.

Try providing the target partition name as "p1",

enterprisedb@edb  ALTER TABLE test_hash
EXCHANGE PARTITION p1
WITH TABLE enterprisedb.change;
ALTER TABLE


For exact partition name and details you can check the table "all_tab_partitions".

View solution in original post

Gold Adventurer

Re: ALTER TABLE...ADD PARTITION on HASH partition on documentation is ok?

thanks!!!, now function OK  (name of partition from all_tab_partitions

 

Regards