Lab on Hash partitioning in Oracle 18C

I have already discussed on RANGE PARTITIONING and LIST PARTITIONING in my previous posts. Oracle also supports Hash based partitioning for fragmentation. It can do so in order to create the main partitions or for sub-partitions it further in conjunction with RANGE-HASH, HASH-RANGE, LIST-HASH, HASH-LIST, or HASH-HASH partitioning.

CREATE TABLE SALES
(
customer_id NUMBER,
sales_date DATE,
order_amount NUMBER,
region NVARCHAR2(10)
)
PARTITION BY HASH(CUSTOMER_ID)
(
partition p1 ,
partition p2,
partition p3,
partition p4
);

Now you insert multiple records in this table.

INSERT INTO sales
VALUES
(10, '11-DEC-2014', 2100,'WEST');
INSERT INTO sales
VALUES
(120, '12-JUN-2015', 1300,'EAST');
INSERT INTO sales
VALUES
(130, '3-FEB-2015', 11200,'NORTH');
INSERT INTO sales
VALUES
(140, '16-FEB-2015', 21149,'EAST');
INSERT INTO sales
VALUES
(150, '10-OCT-2016', 21180,'WEST');
INSERT INTO sales
VALUES
(161, '17-MAR-2019', 22010,'SOUTH');
INSERT INTO sales
VALUES
(171, '17-DEC-2020', 3211,'WEST');
INSERT INTO sales
VALUES
(181, '17-FEB-2018', 4322,'EAST');
INSERT INTO sales
VALUES
(191, '17-FEB-2021', 5433,'WEST');
-- let's commit the data on the disk
COMMIT;
-- view the data normally
SELECT * FROM sales;
-- view the data partition wise
SELECT * FROM sales PARTITION(p4);

Here, the Oracle will use hashing internally to determine in which partition the data will be be stored. Make sure that the tables that you had already created should be removed if the above command has to work, or I would recommend to use the following PL/SQL block to remove each of the tables in the user’s schema that starts with ‘SALES’:

begin
  for rec in (select table_name 
              from   user_tables 
              where  table_name like 'SALES%'
             )
  loop
    execute immediate 'drop table '||rec.table_name;
  end loop;             
end;
/

Instead, you could create the partitions without specifying the partition name manually. In this case, the name of the partition is system generated.

...
PARTITION BY HASH (customer_id) PARTITIONS 8;

HASH-RANGE

...
PARTITION BY HASH (customer_id) SUBPARTITION BY RANGE (order_amount) PARTITIONS 5;

You can verify the partitions by querying the ALL_TAB_SUBPARTITIONS view or USER_TAB_SUBPARTITIONS views using the following commands.

SELECT table_name, partition_name, subpartition_name FROM
USER_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;

TO conclude, in the similar way, depending on the need, the database table can be fragmented even based on the tablespaces, using LIST-HASH, HASH-LIST, and HASH-HASH sub-partitioning.