What is partitioned table? What are its types? Explain its purpose and how to create.
Partitioning allows decomposing large tables and indexes into smaller manageable units called partitions. Queries and DML statement don’t need to be modified to work with them. DDL statements can access and manipulate individual partitions rather than entire table/index providing flexibility and manageability. It is entirely transparent to applications.
Purpose of partitioning:- Need for data management operations, backup operations etc at partition level.
- Improving performance in a database with huge tables.
- Need to reduce the impact of scheduled downtime for maintenance operations.
- Need to increase availability.
- Enhance maintainability and manageability without impacting applications.
Types of partitions:-
Range partitioning: Maps data to partitions based on ranges of partition key values that are established for each partition.
-
List partitioning: Specify a list of discrete values for the partitioning key in the description or each partition. One can then group and organize unordered and unrelated sets of data.
-
Hash partitioning: Enables easy partitioning of data that does not lend itself to range or list partitioning. Easy to implement and better than range counterpart.
-
Composite partitioning: Partitions data sing range partitioning and within each partition, subpartitions them using hash or list method.
Syntax:CREATE TABLE SaleTable
(my_id NUMBER(5),
my_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(my_id)
PARTITIONS 4
STORE IN (data1, data2, data3, data4);
