Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.
When creating an interval partition in a child table, the partition name is inherited from the associated parent table fragment. If the child table has a table-level default tablespace, then it is used as tablespace for the new interval partition; otherwise, the tablespace is inherited from the parent table fragment.
Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.
- Create a parent table and local index.
SQL>create table reserve
(
res_id number primary key not null,
res_date date,
hotel_id number(3),
guest_id number
)
partition by range (res_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(partition P_FIRST values less than (TO_DATE('1900-01-01','YYYY-MM-DD'))
);
Table created.
SQL> create index idx_res_date on reserve(res_date) local;
Index created.
- Create a child table.
SQL>create table transactions
(
trans_id number not null,
res_id number not null,
trans_date date not null,
amt number,
constraint fk_trans_01 foreign key (res_id) references reserve
) partition by reference (fk_trans_01);
Table created.
- Query table and index partitions.
SQL> select table_name, partition_name, high_value
from user_tab_partitions where table_name in ('RESERVE', 'TRANSACTIONS');
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- ---------------------------------------------------------
RESERVE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS P_FIRST
SQL>select INDEX_NAME,PARTITION_NAME,HIGH_VALUE
from user_ind_partitions;
INDEX_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- --------------------------------------
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
- Insert data into parent table
SQL> insert into reserve values (1,sysdate-5,1,1);
1 row created.
SQL> insert into reserve values (2,sysdate-4,1,2);
1 row created.
SQL> insert into reserve values (3,sysdate-3,2,3);
1 row created.
SQL> insert into reserve values (4,sysdate,3,3);
1 row created.
SQL> commit;
Commit complete.
- Query table and index partitions.
SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('RESERVE', 'TRANSACTIONS');
TABLE_NAME PARTITION_NAME HIGH_VALUE
----------- --------------- ---------------------------------------
RESERVE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE
from user_ind_partitions;
INDEX_NAME PARTITION_NAME HIGH_VALUE
------------ -------------- ---------------------------------------------------------
IDX_RES_DATE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
- Insert data into child table.
SQL> insert into transactions values(1,1,sysdate-5,1000);
1 row created.
SQL> insert into transactions values(2,2,sysdate-4,2000);
1 row created.
SQL> insert into transactions values(3,3,sysdate-3,3000);
1 row created.
SQL> insert into transactions values(4,4,sysdate,4000);
1 row created.
SQL> commit;
Commit complete.
- Query tables partitions.
SQL> select table_name, partition_name, high_value
from user_tab_partitions where table_name in ('RESERVE', 'TRANSACTIONS');
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------------------------------------
RESERVE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS P_FIRST
TRANSACTIONS SYS_P301
TRANSACTIONS SYS_P321
TRANSACTIONS SYS_P341
TRANSACTIONS SYS_P342
10 rows selected.
- Drop a partition of parent table.
SQL> alter table RESERVE drop partition SYS_P301 update indexes;
Table altered.
SQL> select table_name, partition_name
from user_tab_partitions
where table_name in ( 'RESERVE','TRANSACTIONS');
TABLE_NAME PARTITION_NAME --------------- --------------- RESERVE P_FIRST RESERVE SYS_P321 RESERVE SYS_P341 RESERVE SYS_P342 TRANSACTIONS P_FIRST TRANSACTIONS SYS_P321 TRANSACTIONS SYS_P341 TRANSACTIONS SYS_P342
8 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME from user_ind_partitions;
INDEX_NAME PARTITION_NAME --------------- --------------- IDX_RES_DATE SYS_P321 IDX_RES_DATE SYS_P341 IDX_RES_DATE SYS_P342 IDX_RES_DATE P_FIRST
We can see both index partition and child partition are dropped automatically.
Make DBA Life Easy 