Hash partitioning is a partitioning technique where a hash key generated through a hashing algorithm is used to distribute rows evenly across the different partitions.Here focus is on data distribution rather than data grouping.
• The partitioning key must be unique.
• This is typically used where ranges are not appropriate, i.e. employee ID, productID, etc.
Use hash partition when you plan to perform partition wise joins with likely equal sized partition.
SQL> CREATE TABLE transaction_demo
2 (
3 transaction_id INT,
4 employee_id INT,
5 team_id INT,
6 customer_id INT,
7 product_id INT,
8 location_id INT,
9 transaction_type VARCHAR2(10),
10 amount INT,
11 profit INT,
12 tran_date DATE,
13 region VARCHAR2(4)
14 )
15 PARTITION BY HASH(transaction_id)
16 (
17 PARTITION P_1,
18 PARTITION P_2,
19 PARTITION P_3,
20 PARTITION P_4
21 );
Table created.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';
no rows selected
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TRANSACTION_DEMO';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------------- -------------- ---------
TRANSACTION_DEMO P_1
TRANSACTION_DEMO P_2
TRANSACTION_DEMO P_3
TRANSACTION_DEMO P_4
SQL> INSERT ALL
2 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (1,1,1,1,1,1,'TEST1',100,100,SYSDATE,'AP')
3 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (2,2,2,2,2,2,'TEST2',200,200,SYSDATE,'EMEA')
4 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (3,3,3,3,3,3,'TEST3',300,300,SYSDATE,'NA')
5 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (4,4,4,4,4,4,'TEST4',400,400,SYSDATE,'AP')
6 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (5,5,5,5,5,5,'TEST5',500,500,SYSDATE,'EMEA')
7 INTO transaction_demo (transaction_id,employee_id,team_id,customer_id,product_id,location_id,transaction_type,amount,profit,tran_date,region) VALUES (6,6,6,6,6,6,'TEST6',600,600,SYSDATE,'NA')
8 SELECT * FROM dual;
6 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';
SEGMENT_NAME BLOCKS MB
----------------- ------- ----------
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TRANSACTION_DEMO';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------------- -------------- ---------
TRANSACTION_DEMO P_1
TRANSACTION_DEMO P_2
TRANSACTION_DEMO P_3
TRANSACTION_DEMO P_4
SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TRANSACTION_DEMO';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------------- -------------- ---------
TRANSACTION_DEMO P_1 1
TRANSACTION_DEMO P_2 0
TRANSACTION_DEMO P_3 2
TRANSACTION_DEMO P_4 3
SQL> SELECT * FROM transaction_demo;
TRANSACTION_ID EMPLOYEE_ID TEAM_ID CUSTOMER_ID PRODUCT_ID LOCATION_ID TRANSACTION_TYPE AMOUNT PROFIT TRAN_DATE REGION
-------------- -------------- ------- ----------- ---------- ----------- -------------------- ----------
6 6 6 6 6 6 TEST6 600 600 27-OCT-21 NA
2 2 2 2 2 2 TEST2 200 200 27-OCT-21 EMEA
5 5 5 5 5 5 TEST5 500 500 27-OCT-21 EMEA
1 1 1 1 1 1 TEST1 100 100 27-OCT-21 AP
3 3 3 3 3 3 TEST3 300 300 27-OCT-21 NA
4 4 4 4 4 4 TEST4 400 400 27-OCT-21 AP
6 rows selected.
SQL> SELECT * FROM transaction_demo PARTITION(P_1);
TRANSACTION_ID EMPLOYEE_ID TEAM_ID CUSTOMER_ID PRODUCT_ID LOCATION_ID TRANSACTION_TYPE AMOUNT PROFIT TRAN_DATE REGION
-------------- -------------- ------- ----------- ---------- ----------- -------------------- ----------
6 6 6 6 6 6 TEST6 600 600 27-OCT-21 NA
SQL> SELECT * FROM transaction_demo PARTITION(P_2);
no rows selected
SQL> SELECT * FROM transaction_demo PARTITION(P_3);
TRANSACTION_ID EMPLOYEE_ID TEAM_ID CUSTOMER_ID PRODUCT_ID LOCATION_ID TRANSACTION_TYPE AMOUNT PROFIT TRAN_DATE REGION
-------------- -------------- ------- ----------- ---------- ----------- -------------------- ----------
2 2 2 2 2 2 TEST2 200 200 27-OCT-21 EMEA
5 5 5 5 5 5 TEST5 500 500 27-OCT-21 EMEA
SQL> SELECT * FROM transaction_demo PARTITION(P_4);
TRANSACTION_ID EMPLOYEE_ID TEAM_ID CUSTOMER_ID PRODUCT_ID LOCATION_ID TRANSACTION_TYPE AMOUNT PROFIT TRAN_DATE REGION
-------------- -------------- ------- ----------- ---------- ----------- -------------------- ----------
1 1 1 1 1 1 TEST1 100 100 27-OCT-21 AP
3 3 3 3 3 3 TEST3 300 300 27-OCT-21 NA
4 4 4 4 4 4 TEST4 400 400 27-OCT-21 AP
SQL> CREATE TABLE summary
2 (
3 transaction_id INT,
4 employee_id INT,
5 customer_id INT,
6 product_id INT,
7 order_date DATE,
8 completion_date DATE,
9 completion_status VARCHAR2(40)
10 )
11 PARTITION BY HASH(transaction_id)
12 (
13 PARTITION P_1_S,
14 PARTITION P_2_S,
15 PARTITION P_3_S,
16 PARTITION P_4_S
17 );
Table created.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'SUMMARY';
no rows selected
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'SUMMARY';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------- --------------- ---------
SUMMARY P_1_S
SUMMARY P_2_S
SUMMARY P_3_S
SUMMARY P_4_S
SQL> INSERT ALL
2 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (1,1,1,1,SYSDATE,SYSDATE,'A')
3 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (2,2,2,2,SYSDATE,SYSDATE,'B')
4 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (3,3,3,3,SYSDATE,SYSDATE,'C')
5 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (4,4,4,4,SYSDATE,SYSDATE,'D')
6 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (5,5,5,5,SYSDATE,SYSDATE,'E')
7 INTO summary (transaction_id,employee_id,customer_id,product_id,order_date,completion_date,completion_status) VALUES (6,6,6,6,SYSDATE,SYSDATE,'F')
8 SELECT * FROM dual;
6 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'SUMMARY';
SEGMENT_NAME BLOCKS MB
------------- ------- ----------
SUMMARY 1024 8
SUMMARY 1024 8
SUMMARY 1024 8
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'SUMMARY';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------- --------------- ---------
SUMMARY P_1_S
SUMMARY P_2_S
SUMMARY P_3_S
SUMMARY P_4_S
SQL> EXEC dbms_stats.gather_table_stats('COURSE','SUMMARY');
PL/SQL procedure successfully completed.
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'SUMMARY';
TABLE_NAME PARTITION_NAME NUM_ROWS
----------- --------------- ---------
SUMMARY P_1_S 1
SUMMARY P_2_S 0
SUMMARY P_3_S 2
SUMMARY P_4_S 3
SQL> SELECT * FROM summary;
TRANSACTION_ID EMPLOYEE_ID CUSTOMER_ID PRODUCT_ID ORDER_DATE COMPLETION_DATE COMPLETION_STATUS
-------------- ----------- ----------- ---------- ---------- --------------- -----------------
6 6 6 6 27-OCT-21 27-OCT-21 F
2 2 2 2 27-OCT-21 27-OCT-21 B
5 5 5 5 27-OCT-21 27-OCT-21 E
1 1 1 1 27-OCT-21 27-OCT-21 A
3 3 3 3 27-OCT-21 27-OCT-21 C
4 4 4 4 27-OCT-21 27-OCT-21 D
6 rows selected.
SQL> SELECT * FROM summary PARTITION(P_1_S);
TRANSACTION_ID EMPLOYEE_ID CUSTOMER_ID PRODUCT_ID ORDER_DATE COMPLETION_DATE COMPLETION_STATUS
-------------- ----------- ----------- ---------- ---------- --------------- -----------------
6 6 6 6 27-OCT-21 27-OCT-21 F
SQL> SELECT * FROM summary PARTITION(P_2_S);
no rows selected
SQL> SELECT * FROM summary PARTITION(P_3_S);
TRANSACTION_ID EMPLOYEE_ID CUSTOMER_ID PRODUCT_ID ORDER_DATE COMPLETION_DATE COMPLETION_STATUS
-------------- ----------- ----------- ---------- ---------- --------------- -----------------
2 2 2 2 27-OCT-21 27-OCT-21 B
5 5 5 5 27-OCT-21 27-OCT-21 E
SQL> SELECT * FROM summary PARTITION(P_4_S);
TRANSACTION_ID EMPLOYEE_ID CUSTOMER_ID PRODUCT_ID ORDER_DATE COMPLETION_DATE COMPLETION_STATUS
-------------- ----------- ----------- ---------- ---------- --------------- -----------------
1 1 1 1 27-OCT-21 27-OCT-21 A
3 3 3 3 27-OCT-21 27-OCT-21 C
4 4 4 4 27-OCT-21 27-OCT-21 D
--A partition wise join is a join between two partitions across tables that are partitioned on the same partitioning key.
--Partition Join Between TRANSACTION_DEMO (P_1) & SUMMARY(P_1_S)
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT t.transaction_id,s.completion_date,s.completion_status FROM transaction_demo t,summary s WHERE t.transaction_id = s.transaction_id and t.transaction_id = 6;
TRANSACTION_ID COMPLETIO COMPLETION_STATUS
-------------- --------- -------------------
6 27-OCT-21 F
Execution Plan
----------------------------------------------------------
Plan hash value: 1297358362
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 447 (1)| 00:00:01 | | |
|* 1 | HASH JOIN | | 1 | 16 | 447 (1)| 00:00:01 | | |
| 2 | PART JOIN FILTER CREATE| :BF0000 | 1 | 3 | 206 (0)| 00:00:01 | | |
| 3 | PARTITION HASH SINGLE | | 1 | 3 | 206 (0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | TRANSACTION_DEMO | 1 | 3 | 206 (0)| 00:00:01 | 1 | 1 |
| 5 | PARTITION HASH SINGLE | | 1 | 13 | 240 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | SUMMARY | 1 | 13 | 240 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."TRANSACTION_ID"="S"."TRANSACTION_ID")
4 - filter("T"."TRANSACTION_ID"=6)
6 - filter("S"."TRANSACTION_ID"=6)
