Let us understand how we can take care of Hash partitioning of tables.
It is primarily used to create partitions based up on modulus and reminder.
Here are the steps involved in creating table using hash partitioning strategy.
Create table using
PARTITION BY HASH
Add default and remainder specific partitions based up on modulus.
Validate by inserting data into the table
We can detach as well as drop the partitions from the table.
Hash partitioning is typically done on sparse columns such as
If we want to use hash partitioning on more than one tables with common key, we typically partition all the tables using same key.
Let us create partitioned table with name
It contains same columns as
We will partition the table based up on
We will create one partition for each reminder with modulus 8.
%%sql CREATE TABLE users_hash_part ( user_id SERIAL, user_first_name VARCHAR(30) NOT NULL, user_last_name VARCHAR(30) NOT NULL, user_email_id VARCHAR(50) NOT NULL, user_email_validated BOOLEAN DEFAULT FALSE, user_password VARCHAR(200), user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A is_active BOOLEAN DEFAULT FALSE, created_dt DATE DEFAULT CURRENT_DATE, last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id) ) PARTITION BY HASH(user_id)