|
Oracle 11g: New Features - System Partitioning
I will assume that you know about partitioning in general.
If not, you can read the following link:
Partitioning
On Oracle 11g, there have been lots of enhancements such as reference
partition, interval partitioning, partitioning virtual columns, and
extended composite partitioning.
System Partitioning
This partitioning will be controlled by the application. It controls
which partition a record should goes.
The following is an example of system partitioning.
CREATE TABLE company-sales
(
SALES_ID NUMBER,
PRODUCT_CODE NUMBER,
STATE_CODE NUMBER
)
PARTITION BY
SYSTEM
(
PARTITION P1 TABLESPACE USERS,
PARTITION P2 TABLESPACE USERS2,
PARTITION P3 TABLESPACE USERS3,
PARTITION P4 TABLESPACE USERS4
);
Oracle 11g: New Features - Interval Partitioning
On Oracle 11g, there have been lots of enhancements such as reference
partition, interval partitioning, partitioning virtual columns, and
extended composite partitioning.
Interval Partitioning
Interval partitioning lets you to create your partitions based on
intervals of the values of the partition key column. The following is an
example of an interval partitioning.
CREATE TABLE company-sales
(
SALES_ID NUMBER,
SALES_DT DATE
)
PARTITION BY RANGE (SALES_DT)
(
PARTITION P001 VALUES
LESS THAN
(TO_DATE('2010-01-01','YYYY-MM-DD')),
PARTITION P002 VALUES
LESS THAN
(TO_DATE('2010-02-01','YYYY-MM-DD')),
PARTITION P003 VALUES
LESS THAN
(TO_DATE('2010-03-01','YYYY-MM-DD')),
PARTITION P004 VALUES
LESS THAN
(TO_DATE('2010-04-01','YYYY-MM-DD')),
PARTITION P005 VALUES
LESS THAN
(TO_DATE('2010-05-01','YYYY-MM-DD')),
PARTITION P006 VALUES
LESS THAN
(TO_DATE('2010-06-01','YYYY-MM-DD')),
PARTITION P007 VALUES
LESS THAN
(TO_DATE('2010-07-01','YYYY-MM-DD')),
PARTITION P008 VALUES
LESS THAN
(TO_DATE('2010-08-01','YYYY-MM-DD')),
PARTITION P008 VALUES
LESS THAN
(TO_DATE('2010-09-01','YYYY-MM-DD')),
PARTITION P009 VALUES
LESS THAN
(TO_DATE('2010-10-01','YYYY-MM-DD')),
PARTITION P010 VALUES
LESS THAN
(TO_DATE('2010-11-01','YYYY-MM-DD')),
PARTITION P011 VALUES
LESS THAN
(TO_DATE('2010-12-01','YYYY-MM-DD'))
);
Oracle 11g: New Features - Reference Partitioning
Reference Partitioning
You create reference portioning when your tables do not have a column
that can be shared. In Oracle 11g database, you can do this by using a
new feature called Reference Partitioning. The following SQL statement
is an example of how to create Reference Partitioning.
CREATE TABLE company-sales
(
SALES_ID NUMBER PRIMARY KEY,
CUST_ID NUMBER NOT NULL,
SALES_AMT NUMBER,
CONSTRAINT FK_SALES_01
FOREIGN KEY (CUST_ID)
REFERENCES
CUSTOMERS
)
PARTITION BY REFERENCE (FK_SALES_01);
Check your partition's type by querying the following sql statement.
SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, REF_PTN_CONSTRAINT_NAME
2 FROM USER_PART_TABLES
3 WHERE TABLE_NAME IN ('CUSTOMERS','COMPANY-SALES');
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------------------
CUSTOMERS LIST
company-sales REFERENCE FK_SALES_01
Good Luck!
|