In Oracle PL/SQL, using a PARTITION is a way to split a large table into smaller segments ("partitions"). Each partition is known by its specific name and has its own characteristics such as its storage and index. Partitioning is done based on a 'Partitioning Key', which is a column or set of columns from the same table whose consolidated value decide the partition for a given data. An index on a partition is known as Local Index, while an index which applies to a full table is known as a Global Index.
The primary objective of Oracle partitioning is to achieve better performance by excluding large volumes of data from query searches. A query to a partitioned table queries only the specific partition instead of scanning the full table. In addition, partitioning provides data archiving options based on range, dates, list and other parameters.
Listed below are the Partitioning methods available in Oracle PL/SQL.
- Interval Partitioning: Partitioning based on only one column of NUMBER or DATE type to specify Range, Hash and List. Interval Partitioning is not supported for Index Organized tables
- System Partitioning: Partitioning based on data movement into the tablespace(s)
- Composite Partitioning: Partitioning based on column(s) to specify range, hash or list
- Virtual Column Based Partitioning: Partitions based on Virtual Column value of the table
- Reference Partitioning: Partitioning based on Referential integrity. Reference Partitioning equi-partitions two tables based on a foreign key.
The table T_WAREHOUSE captures the warehouse details from different locations. The LOC_CODE attribute is used to split the table with respect to the country codes. This demonstrates List Partitioning.
CREATE TABLE T_WAREHOUSE
LOC_CODE VARCHAR2(50 BYTE),
MATERIAL_CODE VARCHAR2(100 BYTE),
DESCRIPTION VARCHAR2(4000 BYTE),
PARTITION BY LIST (LOC_CODE)
PARTITION GRABB VALUES ('IND'),
PARTITION CNILX VALUES ('NZ'),
PARTITION NOAAS VALUES ('SL'),
PARTITION SGIND VALUES ('RSA'),
PARTITION SAARA VALUES ('PAK'),
PARTITION MYABB VALUES ('USA')
This example shows creating a Referential Partitioned table , where different values are stored in different tablespaces according to their value.
CREATE TABLE ref_parent (
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100) TABLESPACE part1,
PARTITION num_rows2 VALUES LESS THAN (1000) TABLESPACE part2,
PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACE part3,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);
Related Code Snippets:
- OVER PARTITION BY - This demo returns employees that are making above average salary in their resp...