How to Create Reference Partitioning in Oracle 11g (Example)

The TEST1 table will be the parent and therefore must be created as a partitioned table.And table TEST_TAB will be the child to TEST1.

SQL> CREATE TABLE TEST1
( user_id INTEGER NOT NULL,
  update_date  TIMESTAMP,
  station_name VARCHAR(50) NOT NULL,
  create_date TIMESTAMP NOT NULL,
  CONSTRAINT TEST1_pk PRIMARY KEY (user_id) )
tablespace data logging  monitoring
partition by range (update_date)
  (partition P0 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA,
   partition P1 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA,
   partition P2 VALUES LESS THAN (TO_DATE(' 2008-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA,
   partition P4 VALUES LESS THAN (TO_DATE(' 2009-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA);

Table created.

SQL> CREATE TABLE TEST_TAB
( character_id NUMBER NOT NULL,
  first_name VARCHAR(15) NOT NULL,
  user_id INTEGER NOT NULL,
  last_played TIMESTAMP,
  last_login TIMESTAMP,
  create_date TIMESTAMP NOT NULL,
  zone_id INTEGER,
  web_image_id INTEGER,
  update_date TIMESTAMP,
  update_user VARCHAR(30),
CONSTRAINT TEST_TAB_pk PRIMARY KEY (character_id),
CONSTRAINT TEST_TAB_fk1 FOREIGN KEY (user_id) REFERENCES TEST1(user_id))
PARTITION BY REFERENCE (TEST_TAB_fk1);

An additional example with Parent, Child, and Grandchild:

-- Create the following tree of tables:

     t0
   /    \
 t1     t3
 /
t2

create table t0
(i int, j int, k int, l int,
 constraint t0_uniq_j unique(j),
 constraint t0_uniq_k_l unique(k, l)
)
partition by range(i)
(partition p1 values less than (10),
 partition p2 values less than (20),
 partition p3 values less than (MAXVALUE));

create table t1
(i int, j int not null,
 constraint t1_uniq_i unique(i),
 constraint t1_fk foreign key(j) references t0(j)
)
partition by reference(t1_fk);

create table t2
(i int, j int not null,
 constraint t2_fk foreign key(j) references t1(i)
)
partition by reference(t2_fk);

create table t3
(i int, j int not null, k int not null,
constraint t3_fk foreign key(j, k) references t0(k, l)
)
partition by reference(t3_fk);

insert into t0 values(25, 0, 0, 0);
insert into t1 values(0, 0);
insert into t2 values(0, 0);
insert into t3 values(0, 0, 0);
insert into t0 values(35, 1, 1, 1);
insert into t1 values(1, 1);
insert into t2 values(1, 1);
insert into t3 values(1, 1, 1);
commit;

before SPLIT PARTITION: - partition p3 has two rows in each table - each table has three partitions

select * from t0 partition (p3);
select * from t1 partition (p3);
select * from t2 partition (p3);
select * from t3 partition (p3);

select table_name, partition_name from user_tab_partitions
order by table_name, partition_position;

alter table t0 split partition p3 at (30) into (partition p3, partition p4);

after SPLIT PARTITION: - partition p3 has one row in each table, - partition p4 has one row in each table. - each table has four partitions

select * from t0 partition (p3);
select * from t1 partition (p3);
select * from t2 partition (p3);
select * from t3 partition (p3);
select * from t0 partition (p4);
select * from t1 partition (p4);
select * from t2 partition (p4);
select * from t3 partition (p4);

select table_name, partition_name from user_tab_partitions
order by table_name, partition_position;

Note

A reference partitioned table can be related to multiple parent tables via referential constraints. However, you have to pick one parent table to equipartition with. The constraint referenced in the PARTITION BY REFERENCE clause will determine which parent table we equipartition with. The partitioning of the other parent tables does not affect the reference partitioned table.

It’s also possible to have a primary key in the ref partitioned table that is referenced by a foreign key in a table that is not ref partitioned.

TRUNCATE PARTITION does not cascade from parent tables to reference partitioned child tables. Instead users must explicitly run the TRUNCATE PARTITION against each table, starting from the child table before proceeding to the parent.

How to Create Reference Partitioning in Oracle 11g (Example)

Note

Latest Posts