HASH PARTITIONING SE14

[Former Member]

Hi,

I need to partition tables on our netweaver 2004s erp. We are running on oracle 10 and i need to solve a question. My question is about hash partitioning option. When we go to SE14 and create the partitions, we select hash partitioning and then the transaction creates fields to input partition values. There is a field "number" that i don´t know how works. If you select a number for example 4, then this creates 4 partitions ??. I have this doubt cause you can too click the + icon and this creates a new partition so, somebody knows what is the function of the number field ?. So i need to know if we must create 4 partitions with number set to 1 or only one partition with number field set to 4 ...

best regards,

carlos castro

[stefan_koehler · Accepted solution]

Hello Carlos,

There is a field "number" that i don´t know how works. If you select a number for example 4, then this creates 4 partitions ??

Which field do you mean? I only have a field called "QUANTITY".. this is the number of partitions that are created by oracle.

You have 2 options to create a hash partitoned table:

1) You only specify the QUANTITY - Oracle will create n-partitions with a system generated name

2) You can specify each partition by your own with your name and settings

Here a little proof with my test table ZMYTEST_K:

SE14 Settings:
PARTITION BY         HASH
COLUMN LIST          ID
QUANTITY             4
TABLESPACE           PSAPSR3USR

SE14 Creating Protocol:
 PARTITION BY HASH ("ID")
 PARTITIONS 4 STORE IN
 ("PSAPSR3USR")

Verify the database object:
SQL>SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT FROM DBA_PART_TABLES WHERE TABLE_NAME ='ZMYTEST_K'; 
TABLE_NAME                     PARTITIONING_TYPE PARTITION_COUNT        
------------------------------ ----------------- ---------------------- 
ZMYTEST_K                      HASH              4                      

SQL>SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT  FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME ='ZMYTEST_K';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT     
------------------------------ ------------------------------ ---------------------- 
ZMYTEST_K                      SYS_P6                         0                      
ZMYTEST_K                      SYS_P7                         0                      
ZMYTEST_K                      SYS_P8                         0                      
ZMYTEST_K                      SYS_P9                         0                                       


And now the other case without the quantity and manual defined partitions:
SE14 Settings:
PARTITION BY         HASH
COLUMN LIST          ID
PARTITION
    PARTITION NAME       TEST_PART1
    QUANTITY
    TABLESPACE           PSAPSR3USR
PARTITION
    PARTITION NAME       TEST_PART2
    QUANTITY
    TABLESPACE           PSAPSR3USR
PARTITION
    PARTITION NAME       TEST_PART3
    QUANTITY
    TABLESPACE           PSAPSR3USR
PARTITION
    PARTITION NAME       TEST_PART4
    QUANTITY
    TABLESPACE           PSAPSR3USR

SE14 Creating Protocol:
PARTITION BY HASH ("ID")
(
PARTITION "TEST_PART1"
TABLESPACE "PSAPSR3USR",
PARTITION "TEST_PART2"
TABLESPACE "PSAPSR3USR",
PARTITION "TEST_PART3"
TABLESPACE "PSAPSR3USR",
PARTITION "TEST_PART4"
TABLESPACE "PSAPSR3USR")

Verify the database object:
SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT FROM DBA_PART_TABLES WHERE TABLE_NAME ='ZMYTEST_K'; 
TABLE_NAME                     PARTITIONING_TYPE PARTITION_COUNT        
------------------------------ ----------------- ---------------------- 
ZMYTEST_K                      HASH              4                      

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT  FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME ='ZMYTEST_K';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT     
------------------------------ ------------------------------ ---------------------- 
ZMYTEST_K                      TEST_PART1                     0                      
ZMYTEST_K                      TEST_PART2                     0                      
ZMYTEST_K                      TEST_PART3                     0                      
ZMYTEST_K                      TEST_PART4                     0                 

The oracle documentation says the same:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2095331

Use the hash_partitioning clause to specify that the table is to be partitioned using the hash method.

Oracle Database assigns rows to partitions using a hash function on values found in columns designated as the partitioning key. You can specify individual hash partitions or you can specify how many subpartitions the database should create.

>individual_hash_partitions

>Use this clause to specify individual partitions by name. If you omit the partition name, then the database assigns partition names of the form SYS_Pn.

>hash_partitions_by_quantity

Alternatively, you can specify the number of partitions. In this case, the database assigns partition names of the form SYS_Pn. The STORE IN clause specifies one or more tablespaces where the hash partitions are to be stored. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

Regards

Stefan

[Former Member]

Thank you very much,

I understand that if i want to place each partition to a different tablespace i have to set the tablespace, name and options in each one and don´t sen´t quantity.

best regards,

carlos castro