26 | 06 | 2017
Latest Articles
Popular Articles

Database Design

Hash partitioned tables

User Rating:  / 0
PoorBest 

Hash partitioned tables

 

See also Partition Administration

When we use hash partitioning data is evenly distributed among the partitions assuming the number of partitions is a power of 2 ( so 2, 4, 8, 16, 32, ... )
Oracle uses a hash algorithm for this distribution. Hash partition is typically used for large tables without a business partition key. It is mostly used on primary key columns or foreign key columns with a lot of distinct values used in joins with other tables.

Advantages of hash partitioning are

1. Possible partition pruning, if we query the table with in the where clause the partition key.

2. Possible partition wise joins if we join tables and query the tables with in the where clause the partition key.

3. Possible to store every partition in its dedicated tablespace, on a separate device ( IO )

4. hash partitioning distributes concurrent inserts among partitions, can as such decrease buffer busy waits in an OLTP database with concurrent DML ( read hash partitioning can reduce hot spots )



This is an example os a table which is hash partitioned on the primary key. All the partitions are created in the same tablespace.
The primary key index itself is a local index.

SQL> create table table_T1 (T1_ID number(18),

  2  T1_SOMEFIELD1 varchar2(10),
  3  T1_SOMEFIELD2 varchar2(10),
  4  T1_SOMEFIELD3 varchar2(10),
  5  constraint T1_PK primary key ( T1_ID ) using index local )
  6  partition by hash (T1_ID) partitions 8 tablespace users;

Table created.



This is an example of a table which is hash partitioned on the primary key. Every partition is created in a dedicated tablespace.
The primary key index itself is a local index.

SQL> create table table_T2 (T2_ID number(18),

  2  T2_SOMEFIELD1 varchar2(10),
  3  T2_SOMEFIELD2 varchar2(10),
  4  T2_SOMEFIELD3 varchar2(10),
  5  constraint T2_PK primary key ( T2_ID ) using index local )
  6  partition by hash (T2_ID)
  7   ( partition T2_P01? tablespace user_data1,
  8     partition T2_P02? tablespace user_data2,
  9     partition T2_P03? tablespace user_data3,
 10     partition T2_P04? tablespace user_data4);

Table created.



This is an example of a table which is hash partitioned on the foreign key. All the partitions are created in the same tablespace.
The primary key index itself is not partitioned.
We create a local index on the foreign key column

SQL> create table table_T3 (T3_ID number(18),
  2  T3_T2ID? number(18),
  3  T3_SOMEFIELD1 varchar2(10),
  4  T3_SOMEFIELD2 varchar2(10),
  5  T3_SOMEFIELD3 varchar2(10),
  6  constraint T3_PK primary key ( T3_ID ) using index )
  7  partition by hash (T3_T2ID) partitions 8 tablespace users;

Table created.

SQL> alter table table_T3 add constraint FK_T3_T2 foreign key (T3_T2ID) references table_T2 (T2_ID);

Table altered.

SQL> create index T3_IDX1 on table_T3 (T3_T2ID) local initrans 4 tablespace users;

Index created.


This is an example of a table which is hash partitioned on the foreign key. All the partitions are created in the same tablespace.
The primary key index itself is global hash partitioned.
We create a local index on the foreign key column

SQL> create table table_T4 (T4_ID number(18),

  2  T4_T2ID? number(18),
  3  T4_SOMEFIELD1 varchar2(10),
  4  T4_SOMEFIELD2 varchar2(10),
  5  T4_SOMEFIELD3 varchar2(10),
  6  constraint T4_PK primary key ( T4_ID ) using index global partition by hash
  (T4_ID) tablespace USERS )
  7  partition by hash (T4_T2ID) partitions 8;

Table created.

SQL> create index T4_IDX1 on table_T4 (T4_T2ID) local initrans 4 tablespace users;

Index created.




I will pay attention to partition wise joins in detail elsewhere. Here are some selects which benefits from partition wise joins.

select * from table_T2, table T3 where table T3.T3_T2ID = table_T2.T2_ID and table T2_ID = &number;


select * from table_T2, table T3 where table T3.T3_T2ID = table_T2.T2_ID and table T2_ID in (&number1, &number2, &number3, ... )