Hash partitioning in Oracle !!

by

Hash partitioning is a database partitioning technique used in Oracle databases to distribute data evenly across multiple partitions. This method is particularly useful for ensuring a balanced distribution of data and for improving query performance by minimizing data retrieval times.

In contrast to Range or List Partitioning, where you specify the criteria that determine the partition a row belongs to based on the value in the partition key column(s), Hash Partitioning takes a different approach. With Hash Partitioning, Oracle applies a hashing algorithm to distribute rows “randomly” across the available partitions in the table.

This method is particularly useful when the goal is to divide a table into smaller physical segments (potentially located in different tablespaces on separate disks) without needing to group the data in any particular way.

How Hash Partitioning Works

Partition Key: A hash function is applied to a specified partition key column. The hash function determines the partition into which a given row should be placed.

Hash Function: Oracle uses an internal hash function to map the partition key values to specific partitions. The goal is to distribute rows evenly across all partitions.

Partitions: The result of the hash function determines the partition number. For example, if there are 4 partitions and the hash function results in a value between 0 and 3, the row will be placed in the corresponding partition.

Benefits of Hash Partitioning

Load Balancing: Even distribution of data across partitions helps in avoiding hotspots and ensures balanced workload.

Improved Performance: Since data is evenly distributed, parallel queries can be more efficient, leading to better performance for read and write operations.

Simplified Management: Unlike range partitioning, hash partitioning does not require constant monitoring and management of partition boundaries.

Create 4 Tablespace corresponding to 4 partition.

CREATE TABLESPACE hash_part1
DATAFILE '/u01/app/oracle/oradata/PROD101/hash_part1.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE hash_part2
DATAFILE '/u01/app/oracle/oradata/PROD101/hash_part2.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE hash_part3
DATAFILE '/u01/app/oracle/oradata/PROD101/hash_part3.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE hash_part4
DATAFILE '/u01/app/oracle/oradata/PROD101/hash_part4.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Create 4 Partitions in 4 different Tablespaces:

SQL>  create table table_for_partition
  (Sl_number number,
   Sl_key varchar2(32),
   product_value varchar2(64),
   product_timestamp timestamp)
  partition by hash (Sl_number)
  (partition p1 tablespace hash_part1,
   partition p2 tablespace hash_part2,
   partition p3 tablespace hash_part3,
   partition p4 tablespace hash_part4)
  /

Table created.

Insert 10,000 rows from a single session in Partition table:

SQL> insert into table_for_partition
 select rownum,
 dbms_random.string('X',16),
 dbms_random.string('X',32),
 systimestamp
 from dual
 connect by level < 10001;

10000 rows created.


SQL> commit;
Commit complete.

Gather Table Statistics:

SQL> exec dbms_stats.gather_table_stats('SYS','table_for_partition');

PL/SQL procedure successfully completed.

SQL>

Validate the outputs:

Select random rows from the Partition Table:

SQL> select partition_name, num_rows
from dba_tab_partitions
where table_name = 'TABLE_FOR_PARTITION'
order by partition_position;

PARTITION_NAME	  NUM_ROWS
--------------- ----------
P1			   2471
P2			   2527
P3			   2521
P4			   2481


SQL> select Sl_number
from table_for_partition partition (P1)
where rownum < 10
order by 1;

 SL_NUMBER
----------
      4577
      4580
      4582
      4586
      4596
      4601
      4603
      4604
      4605

9 rows selected.

SQL> select Sl_number
 from table_for_partition partition (P2)
 where rownum < 10
 order by 1;

 SL_NUMBER
----------
      4519
      4522
      4528
      4529
      4537
      4539
      4540
      4544
      4546

9 rows selected.

SQL> select Sl_number
from table_for_partition partition (P3)
where rownum < 10
and Sl_number < 100
order by 1;

 SL_NUMBER
----------
	 2
	 5
	 8
	18
	20
	21
	23
	24
	33

9 rows selected.

SQL> select Sl_number
from table_for_partition partition (P4)
where rownum < 10
and Sl_number between 700 and 1000
order by 1;

 SL_NUMBER
----------
       709
       711
       716
       721
       729
       732
       736
       737
       739

9 rows selected.

Hope it helped !! 🙂