Let us understand how we can manipulate data for a partitioned table.
Key Concept Explanation
In a partitioned table, we can manipulate data by using the table or the specific partitions created for each subset of data. Here are some key concepts to note:
Insert Data
- We can insert data using the main table or table specifically created for a partition.
- For a partitioned table
users_part
, we can use either the table nameusers_part
or the partition nameusers_part_u
to insert records withuser_role
‘U’.
CREATE TABLE users_part_u
PARTITION OF users_part
FOR VALUES IN ('U')
Update Data
- Changing the value in a partitioned column that results in moving data from one partition to another is handled internally.
- The update operation remains the same regardless of the partitioning strategy used (
list
,range
, orhash
).
Delete Data
- Data can be deleted from the main table or the tables created for each partition (
users_part
,users_part_u
,users_part_a
, etc).
Hands-On Tasks
Here are some hands-on tasks you can perform to manipulate data for a partitioned table:
- Truncate the
users_part
table. - Insert records into the
users_part
table. - View data from the
users_part_u
partition. - Insert records into the
users_part_a
partition. - View data from the
users_part
table. - Update user roles in the
users_part
table. - View data from the
users_part_a
partition. - Delete a record from the
users_part
table. - Delete a record from the
users_part_u
partition. - View data from the
users_part
table.
Conclusion
In this article, we learned about manipulating data in a partitioned table by inserting, updating, and deleting records in both the main table and the partitions. It’s essential to understand how to manage data within a partitioned table to maintain an efficient and organized database.
Remember, practice makes perfect, so feel free to try out these tasks and engage with the community for further learning.