Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the range partitions.
You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data beyond that transition point.
Interval partitioning restrictions
Can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is not supported for index-organized tables.
Cannot create a domain index on an interval-partitioned table.
above the transition point of January 1, 2007, partitions are created with a width of one month.
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );
The high bound of partition p3 represents the transition point. p3 and all partitions below it (p0, p1, and p2 in this example) are in the range section while all partitions above it fall into the interval section.
System Partitioning
System partitioning does not have any partitioning keys so the mapping of the rows to a particular partition is not implicit. Instead, you specify the partition to which a row maps by using partition extended syntax when inserting a row.
Without a partitioning key the usual performance benefits of partitioned tables are not available for system partitioned tables. There is no support for traditional partition pruning or partition wise joins. Partition pruning is only achieved by accessing the same partitions in the system partitioned tables as those that were accessed in the base table.
System partitioned tables do provide the manageability advantages of equi-partitioning. For example, a nested table can be created as a system partitioned table that has the same number of partitions as the base table. A domain index can be backed up by a system partitioned table that has the same number of partitions as the base table.
System Partitioning Guidelines
The following operations are supported for system partitioned tables:
* Partition maintenance operations and other DDLs (See exceptions below)
* Creation of local indexes.
* Creation of local bitmapped indexes.
* Creation of global indexes.
* All DML operations.
* INSERT AS SELECT with partition extended syntax:
INSERT INTO
Following operations are not supported for system partitioning
* Unique local indexes are not supported because they require a partitioning key.
* CREATE TABLE AS SELECT
* Since there is no partitioning method, it is not possible to distribute rows to partitions. Instead the user should first create the table and then insert rows into each partition.
INSERT INTO tabname AS subquery
SPLIT PARTITION operations
CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
);
INSERT and MERGE statements must use partition extended syntax to identify a particular partition a row should go into.For example, tuple (4,5) can be inserted into any one of the above four partitions
INSERT INTO systab PARTITION (p1) VALUES (4,5); /* Partition p1 */
INSERT INTO systab PARTITION (1) VALUES (4,5); /* First partition */
INSERT INTO systab PARTITION (:pno) VALUES (4,5); /* pno bound to 1/p1 */
Or:
INSERT INTO systab PARTITION (p2) VALUES (4,5); /* partition p2 */ or
INSERT INTO systab PARTITION (2) VALUES (4,5) /* second partition */
INSERT INTO systab PARTITION (:pno) VALUES (4,5); /* pno bound to 1/p1 */
Composite Partitioning Enhancements
With Oracle Database 11g list partitioning can be a top level partitioning method for composite partitioned tables giving us List-List, List-Hash, List-Range and Range-Range composite methods.
With the Interval partitioning, the following composite partitioning methods are also supported: Interval-Range, Interval-List and Interval-Hash.
* 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.
* 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.
* List-Hash Partitioning :- Composite list-hash partitioning enables hash sub-partitioning of a list-partitioned object; for example, to enable partition-wise joins.
* 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.
eg: Composite Range-Range Partitioning
CREATE TABLE shipments(
order_date DATE NOT NULL,
delivery_date DATE NOT NULL)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
(PARTITION p_2006_jul VALUES LESS THAN
(TO_DATE('01-AUG-2006','dd-MON-yyyy'))
( SUBPARTITION p06_jul_e VALUES LESS THAN
(TO_DATE('15-AUG-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_a VALUES LESS THAN
(TO_DATE('01-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE))
,PARTITION p_2006_aug VALUES LESS THAN
(TO_DATE('01-SEP-2006','dd-MON-yyyy'))
( SUBPARTITION p06_aug_e VALUES LESS THAN
(TO_DATE('15-SEP-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_a VALUES LESS THAN
(TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)));
The above example illustrates how range-range partitioning might be used. The example tracks shipments.
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range.
Virtual Column-Based Partitioning
The values for virtual columns are not physically stored in the table row on disk, rather they are evaluated on demand. The functions or expressions describing the virtual columns should be deterministic and pure, meaning the same set of input values should return the same output values.
Virtual columns can be used like any other table columns. They can be indexed, used in queries, DML and DDL statements. Tables and indexes can be partitioned on a virtual column and even statistics can be gathered upon them.
These columns can be specified during a CREATE, or ALTER table operation and can be defined to be either visible or hidden. Virtual columns share the same SQL namespace as other real table columns and conform to the data type of the underlying expression that describes it. These columns can be used in queries like any other table columns providing a simple, elegant and consistent mechanism of accessing expressions in a SQL statement.
Partition pruning takes place for virtual column partition keys when the predicates on the partitioning key are of the following types:
* Equality or Like
* List
* Range
* TBL$
* Partition extended names
Given a join operation between two tables, the optimizer recognizes when partition-wise join (full or partial) is applicable, decides whether to use it or not and annotate the join properly when it decides to use it. This applies to both serial and parallel cases.
To recognize full partition-wise join the optimizer relies on the definition of equi-partitioning of two objects, this definition includes the equivalence of the virtual expression on which the tables were partitioned.
CREATE TABLE employees
(employee_id number(6) not null, first_name varchar2(30),
last_name varchar2(40) not null, email varchar2(25),
phone_number varchar2(20), hire_date date not null,
job_id varchar2(10) not null, salary number(8,2),
commission_pct number(2,2), manager_id number(6),
department_id number(4),
total_compensation as (salary *( 1+commission_pct))
)
PARTITION BY RANGE (total_compensation)
(
PARTITION p1 VALUES LESS THAN (50000),
PARTITION p2 VALUES LESS THAN (100000),
PARTITION p3 VALUES LESS THAN (150000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
Reference Partitioning
Reference partitioning allows the partitioning of a table based on the partitioning scheme of the table referenced in its referential constraint. The partitioning key is resolved through an existing parent-child relationship, enforced by active primary key or foreign key constraints. This means that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, making application development easier and less error-prone.
To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table.
CREATE TABLE orders
(order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(PARTITION Q1_2005 VALUES LESS THAN
(TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN
(TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN
(TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN
(TO_DATE('01-JAN-2006','DD-MON-YYYY')));
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);
The reference-partitioned child table ORDER_ITEMS example above is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.
If partition descriptors are provided, then the number of partitions described must be exactly equal to the number of partitions or sub-partitions in the referenced table. If the parent table is a composite partitioned table, then the table will have one partition for each sub-partition of its parent; otherwise the table will have one partition for each partition of its parent.
Partition bounds cannot be specified for the partitions of a reference-partitioned table. The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it will inherit its name from the corresponding partition in the parent table, unless this inherited name conflicts with one of the explicit names given. In this case, the partition will have a system-generated name.
Partitions of a reference-partitioned table will co-locate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.
