Hash Partitioning with Examples in Oracle

September 9, 2017
Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions. Hash partitioning is used where ranges aren’t appropriate, i.e. employee number, customer ID, etc. Using this approach, data is randomly distributed across the partitions rather than grouped.

How to Create Hash Partition Table in Oracle

  • Choose a column that is unique.
  • Create multiple partitions and subpartitions for each partition that is a power of two. For example, 2, 4, 8, 16, 32, 64, 128, and so on.


Following are the Benefits and uses of Hash Partitioning:

  1. To enable partial or full parallel partition-wise joins with likely equisized partitions.
  2. To distribute data evenly among different partitions.
  3. To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.
  4. To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.

Examples to Create Hash Partition Table in Oracle

CREATE TABLE CUSTOMERS
(
RECEIVED_TIME_STAMP     DATE NOT NULL,
NETWORK_ID              VARCHAR2(10) NOT NULL,
RECORD_TYPE             VARCHAR2(50),
CUSTOMER_ID             NUMBER(18) NOT NULL,
PHONE_NUMBER            VARCHAR2(80),
CUSTOMER_NAME           VARCHAR2(80),
ACCOUNT_ID              NUMBER(18),
CUSTOMER_TYPE           VARCHAR2(80),
NRC_NUMBER              VARCHAR2(80),
CUSTOMER_FIRST_NAME     VARCHAR2(80),
CUSTOMER_MIDDLE_NAME    VARCHAR2(80),
CUSTOMER_LAST_NAME      VARCHAR2(80)
)
partition BY hash (CUSTOMER_ID)
(
partition PARTITION P001 tablespace TS_CUSTOMERS_01,
partition PARTITION P002 tablespace TS_CUSTOMERS_02,
partition PARTITION P003 tablespace TS_CUSTOMERS_03,
partition PARTITION P004 tablespace TS_CUSTOMERS_04,
partition PARTITION P005 tablespace TS_CUSTOMERS_01,
partition PARTITION P006 tablespace TS_CUSTOMERS_02,
partition PARTITION P007 tablespace TS_CUSTOMERS_03,
partition PARTITION P008 tablespace TS_CUSTOMERS_04,
partition PARTITION P009 tablespace TS_CUSTOMERS_01,
partition PARTITION P010 tablespace TS_CUSTOMERS_02,
partition PARTITION P011 tablespace TS_CUSTOMERS_03,
partition PARTITION P012 tablespace TS_CUSTOMERS_04,
partition PARTITION P013 tablespace TS_CUSTOMERS_01,
partition PARTITION P014 tablespace TS_CUSTOMERS_02,
partition PARTITION P015 tablespace TS_CUSTOMERS_03,
partition PARTITION P016 tablespace TS_CUSTOMERS_04
);

Example to Create Index on the above Partition Table

CREATE  INDEX IX_CUSTOMER_ID on CUSTOMERS (CUSTOMER_ID) INITRANS 4 STORAGE(FREELISTS 16)
LOCAL
(
PARTITION P001 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P002 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P003 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P004 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P005 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P006 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P007 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P008 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P009 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P010 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P011 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P012 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P013 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P014 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P015 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P016 TABLESPACE TS_CUSTOMER_ID_IDX
);

Must Read: