Interval-Reference Partitioned Tables

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 see both index partition and  child partition are dropped automatically.

Advertisements
This entry was posted in 12c new features and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s