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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
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:

1
2
3
4
5
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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 !! 🙂