Hash partitioning in Oracle !!
3 min readHash 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 !! 🙂
