[心得] Oracle 11g reference partitioning

Oracle 11g reference partitioning 是11g的new feature可以針對parent table , child table同時切割partition,

切child table partition的依據是根據parent的range資訊來切割(parent table是range partition table)

範例如下:

create tablespace oitpmin datafile '/oracle/oradata/orcl/oitpmin.dbf' size 1M autoextend on next 1M;

create tablespace oitp1 datafile '/oracle/oradata/orcl/oitp1.dbf' size 1M autoextend on next 1M;

create tablespace oitp2 datafile '/oracle/oradata/orcl/oitp2.dbf' size 1M autoextend on next 1M;

create tablespace oitp3 datafile '/oracle/oradata/orcl/oitp3.dbf' size 1M autoextend on next 1M;

create tablespace p1 datafile '/oracle/oradata/orcl/p1.dbf' size 1M autoextend on next 1M;

create tablespace p2 datafile '/oracle/oradata/orcl/p2.dbf' size 1M autoextend on next 1M;

create tablespace p3 datafile '/oracle/oradata/orcl/p3.dbf' size 1M autoextend on next 1M;

CREATE TABLE scott.orders (

  order_id    NUMBER PRIMARY KEY,

  order_date  DATE NOT NULL,

  customer_id NUMBER NOT NULL,

  shipper_id  NUMBER)

PARTITION BY RANGE (order_date) (

  PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')) tablespace p1,

  PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')) tablespace p2,

  PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')) tablespace p3);

CREATE TABLE scott.order_items (

  order_id    NUMBER NOT NULL,

  product_id  NUMBER NOT NULL,

  price       NUMBER,

  quantity    NUMBER,

  CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES scott.orders)

PARTITION BY REFERENCE (order_items_fk)

(

partition "oitmin" tablespace oitpmin ,

partition "oit_y1" tablespace oitp1 ,

partition "oit_y2" tablespace oitp2

);

--cleanup

drop table scott.orders;

drop table scott.order_items;

drop tablespace oitpmin including contents and datafiles;

drop tablespace oitp1 including contents and datafiles;

drop tablespace oitp2 including contents and datafiles;

drop tablespace oitp3 including contents and datafiles;

drop tablespace p1 including contents and datafiles;

drop tablespace p2 including contents and datafiles;

drop tablespace p3 including contents and datafiles;

Ref:

http://www.orafaq.com/wiki/Reference_partitioning

Reference partitioning is a partitioning method introduced in Oracle 11g. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table.