What is partitioned table? What are its types and purposes? - oracle

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.

(my_id NUMBER(5),
my_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
STORE IN (data1, data2, data3, data4);
What is the role of Archiver [ARCn]? - oracle
What is the role of Archiver [ARCn]? - ARCn is an oracle background process responsible for copying the entirely filled online redo log file to the archive log...
Difference between bitmap and btree index in oracle
Difference between bitmap and btree index - Btree It is made of branch nodes and leaf nodes. Branch nodes holds prefix key value along with the link to the leaf node. The leaf node in turn...
Convert oracle table data into excel sheet - oracle
Convert oracle table data into excel sheet - There are 2 ways to do so: 1. Simply use the migration wizard shipped with Oracle...
Post your comment