Snippet Name: PARTITIONS
Description: Oracle allows us to ecompose a table or index into smaller, more manageable pieces, called partitions. Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.
Also see: » PARTITIONS
» Create monthly partitions
Comment: Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.
Language:
Highlight Mode: PLSQL
Last Modified: March 16th, 2009
|
PARTITION Key
Each ROW IN a partitioned TABLE IS unambiguously assigned
TO a single PARTITION. The PARTITION key IS a SET OF FROM
1 TO 16 columns that determines the PARTITION FOR each ROW.
Subpartition Partitions created within partitions.
They are just partitions themselves AND there IS nothing
special about them.
Composite Partitioning
Composite partitioning IS a combination OF other partitioning
methods. Oracle currently supports range-hash AND
range-list composite partitioning.
INTERVAL Partitioning
INTERVAL partitioning IS an extension TO RANGE partitioning
IN which, beyond a point IN TIME, partitions are defined
BY an INTERVAL. INTERVAL partitions are automatically created
BY the database WHEN data IS inserted INTO the PARTITION.
Example OF PARTITION BY list -
CREATE TABLE list_part (
deptno NUMBER(10),
quarterly_sales NUMBER(10,2),
state VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION q1_nw VALUES ('OR', 'WA') TABLESPACE part1,
PARTITION q1_sw VALUES ('AZ', 'CA', 'NM') TABLESPACE part2,
PARTITION q1_ne VALUES ('NY', 'VT', 'NJ') TABLESPACE part1,
PARTITION q1_se VALUES ('FL', 'GA') TABLESPACE part2,
PARTITION q1_nc VALUES ('MN', 'WI') TABLESPACE part1,
PARTITION q1_sc VALUES ('OK', 'TX') TABLESPACE part2);
Example OF PARTITION BY DATE ramge -
CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION yr7 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
TABLESPACE part2,
PARTITION yr8 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
TABLESPACE part3,
PARTITION yr9 VALUES LESS THAN (MAXVALUE) TABLESPACE part4); |