Hash partitioning is one of the partitioning method, where hash algorithm is used to distribute data equally among multiple partitions.

It is usually used for large tables, where we can’t use RANGE key, and column contains lot of distinct value.

EXAMPLE:

TABLE WITH HASH PARTITION:(WITH PREDEFINED PARTITION_NAME)

create table DBACLASS (
   DBANO number(4), 
   eDBANAME varchar2(30), 
   CATEG_ID   number
) 
partition by hash(DBANO) (
  partition d1 tablespace DBATS1, 
  partition d2 tablespace DBATS1, 
  partition d3 tablespace DBATS1,
  partition d4 tablespace DBATS1
); 

SQL> select table_name,partition_name from dba_tab_partitions  where table_name='DBACLASS';

TABLE_NAME      PARTITION_NAME
--------------- ---------------
DBACLASS        D1
DBACLASS        D2
DBACLASS        D3
DBACLASS        D4


SQL> select OWNER,TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables where table_name='DBACLASS';

OWNER    TABLE_NAME      PARTITION PARTITION_COUNT STATUS
-------- --------------- --------- --------------- --------
SYS      DBACLASS        HASH                    4 VALID

TABLE WITH HASH PARTITION:(WITH DEFAULT PARTITION_NAME)

While creating hash partitioned , if we don’t define the partitions names, then default partition names like SYS_% will be created.

create table DBACLASS_2 (
   DBANO number(4), 
   eDBANAME varchar2(30), 
   CATEG_ID   number
) 
partition by hash(DBANO) partitions 4;

SQL>  select table_name,partition_name from dba_tab_partitions  where table_name='DBACLASS_2';

TABLE_NAME      PARTITION_NAME
--------------- ---------------
DBACLASS_2      SYS_P594
DBACLASS_2      SYS_P595
DBACLASS_2      SYS_P596
DBACLASS_2      SYS_P597

COMPOSIT HASH PARTITION:

Composit hash partitioning means, A table is divided into multiple partitions, and again each partition is divided multiple sub partitions.

Here we are creating a table, with 4 hash partitions, and each partitions has 8 subpartitions(HASH).

CREATE TABLE DBA_PART (  
DBA_id NUMBER(4) NOT NULL,   
DBA_name VARCHAR2(30),
DBA_CAT NUMBER(4) NOT NULL)  
PARTITION BY HASH(DBA_id)  
SUBPARTITION BY HASH (DBA_CAT) SUBPARTITIONS 8
(
partition d1, 
partition d2,
partition d3,
partition d4
)

SQL>  select table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT  from dba_part_tables where table_name='DBA_PART';

TABLE_NAME      PARTITION SUBPARTIT PARTITION_COUNT
--------------- --------- --------- ---------------
DBA_PART        HASH      HASH                    4

SQL> select partition_name,count(*) from dba_tab_subpartitions where table_name='DBA_PART' group by partition_name;

PARTITION_NAME    COUNT(*)
--------------- ----------
D1                       8
D3                       8
D2                       8
D4