iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 11g New Features

Online Oracle Training

 

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!

 

Google
 
Web web site