What is Partitioning
Partitioning is the way to manage very large database easily and on finer level. Using portioning You can divide tables ,index and IOT into small parts .
Benefits of partitioning
It enhanced the performance, availability and manageability of large databases.A database administrator can manage tables as multiple pieces collectively or individually. There is a greater ability for parallelism with more partitions
1 ) Availability of data:
Partitioning increase the availability of data in production environment i.e if one
Partition is unavailable all other partition of the table are available for accasses.
2) Easier Management
It is easy to manage a small partitions i.e de-fragmentation ,index rebuild move tables then if you manage a single 200gb table .
3) Enhance performance
It may reduce the high contention on same segments and enhance performance .As we can spared the data on many disk using portioning .
4) Partition Pruning
Partition pruning use to improve performance using partitioning.
i.e Oracle table have data of 10 years and you have partitioned it year wise A query requesting orders for a single year would only access a single partition of the oracle table. This query could potentially execute 10 times faster simply because of partition pruning.
When to Partitioned Tables
When to Partitioned Tables
- Tables greater than 2 GB should always be considered as candidates for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
- When the contents of a table need to be distributed across different types of storage devices.
When to Partition an Index
- Avoid rebuilding the entire index when data is removed.
- Perform maintenance on parts of the data without invalidating the entire index.
- Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.
When to Partitioned Index-Organized Tables
- Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.
- For partitioning an index-organized table:
- Partition columns must be a subset of the primary key columns
- Secondary indexes can be partitioned (both locally and globally)
- OVERFLOW data segments are always equi-partitioned with the table partitions
Range partitioning is useful when you have distinct ranges of data you want to store together.
( range_1 NUMBER NOT NULL,
data_date DATE NOT NULL,
)
PARTITION BY RANGE (data_date)
(PARTITION part1 VALUES LESS THAN (TO_DATE('01/01/2011' ,'DD/MM/YYYY')) TABLESPACE tbs1,
PARTITION part2 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) TABLESPACE tbs2);
( range_1 NUMBER NOT NULL,
data_date DATE NOT NULL,
)
PARTITION BY HASH (data_date)
PARTITIONS 2
STORE IN (tbs1, tbs2);
(stu_code NUMBER NOT NULL,
stu_name varchar2(50),
stu_add varchar2(200)
)
PARTITION BY list (stu_name)
(
PARTITION ATON VALUES ('vanita', 'jannat', 'kiran'),
PARTITION NTOZ VALUES ('Sumit'),
);
CREATE TABLE whr_comp
(data_code NUMBER NOT NULL,
data_date DATE NOT NULL
)
PARTITION BY RANGE (data_date
SUBPARTITION BY HASH (data_code)
SUBPARTITIONS
(PARTITION part1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE tbs1,
PARTITION part2 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) TABLESPACE tbs2
);
Composite Range-Range Partitioning
Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.
Composite Range-Hash Partitioning
Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.
Composite Range-List Partitioning
Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.
Composite List-Range Partitioning
Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.
Composite List-Hash Partitioning
Composite list-hash partitioning enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins.
Composite List-List Partitioning
Composite list-list partitioning enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel.
