CHILD TABLE DOESN’T INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLITTING PARENT PARTITION

set “_force_oltp_compress”=true for CHILD TABLE to INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLIT PARTITION

ISSUES

When split a partition of a reference parent table, the compression attribute “compress for OLTP” is not inherited by child partitions. Instead,it changes into “compress for BASIC’ by default.

The following test case is on 11.2.0.4 database. We have two tables named “parent” and “child”, here parent is partitioned by range of a date column, and child table is a partitioned by foreign key reference to parent table.

1) Create two tables.

SQL>CREATE TABLE Parent
    (
     pk_p_id number primary key ,
     P_Date DATE,
     P_cost NUMBER
    ) compress for oltp
    PARTITION BY RANGE (P_Date)
   (PARTITION P_First VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
    PARTITION P_2014_11_01 VALUES LESS THAN (TO_DATE('02-11-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
    PARTITION P_MAXIMUM VALUES LESS THAN (MAXVALUE) COMPRESS for OLTP
   );

Table created.

SQL> CREATE TABLE Child(
pk_c_id number primary key ,
fk_c_id number not null,
c_total NUMBER,
CONSTRAINT Child_FK FOREIGN KEY ( fk_c_id ) REFERENCES parent ( pk_p_id)
) compress for oltp
partition by reference ( Child_FK ) ;

Table created.

2) All partitions are “compress for oltp”

SQL> select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ('PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ----------- ---------------- --------- ------------
CHILD P_FIRST ENABLED OLTP CHILD P_2014_11_01 ENABLED OLTP CHILD P_MAXIMUM ENABLED OLTP PARENT P_FIRST ENABLED OLTP PARENT P_2014_11_01 ENABLED OLTP PARENT P_MAXIMUM ENABLED OLTP 6 rows selected.

3) split parent P_MAXIMUM partition:

SQL> alter table PARENT split partition P_MAXIMUM at (TO_DATE('03-11-2014', 'DD-MM-YYYY')) into ( partition P_2014_11_02, partition P_MAXIMUM) update indexes;

Table altered.

4) New child partitions compression attribute is “compress for BASIC”

SQL>select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ------------ --------------- --------- ------------ CHILD P_FIRST ENABLED OLTP CHILD P_2014_11_01 ENABLED OLTP CHILD P_2014_11_02 ENABLED BASIC CHILD P_MAXIMUM ENABLED BASIC PARENT P_FIRST ENABLED OLTP PARENT P_2014_11_01 ENABLED OLTP PARENT P_2014_11_02 ENABLED OLTP PARENT P_MAXIMUM ENABLED OLTP 8 rows selected.

5) This issue still exists in 12c.

$ sqlplus / as sysdba
SQL*Plus:Release 12.1.0.2.0 Production on Mon Nov 17 07:31:31 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> connect system
Enter password: 
Connected.

SQL> CREATE TABLE Parent 
 ( 
 pk_p_id number primary key , 
 P_Date DATE, 
 P_cost NUMBER 
 ) compress for oltp 
 PARTITION BY RANGE (P_Date) 
 (PARTITION P_First 
 VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
 PARTITION P_2014_11_01 
 VALUES LESS THAN (TO_DATE('02-11-2014', 'DD-MM-YYYY')) COMPRESS for OLTP, 
 PARTITION P_MAXIMUM VALUES LESS THAN (MAXVALUE) COMPRESS for OLTP
 );
Table created.
SQL> CREATE TABLE Child 
( 
pk_c_id number primary key , 
fk_c_id number not null, 
c_total NUMBER, 
CONSTRAINT Child_FK FOREIGN KEY ( fk_c_id ) REFERENCES parent ( pk_p_id)
) compress for oltp 
partition by reference ( Child_FK ) ;

Table created.
SQL> col PARTITION_NAME format a18
SQL> col table_name format a15
SQL> set pagesize 120
SQL> set linesize 160
SQL>select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION;

TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ----------- -------------- -------- ------------ CHILD P_FIRST ENABLED ADVANCED CHILD P_2014_11_01 ENABLED ADVANCED CHILD P_MAXIMUM ENABLED ADVANCED PARENT P_FIRST ENABLED ADVANCED PARENT P_2014_11_01 ENABLED ADVANCED PARENT P_MAXIMUM ENABLED ADVANCED 6 rows selected.
SQL> alter table PARENT split partition P_MAXIMUM at (TO_DATE('03-11-2014', 'DD-MM-YYYY')) into ( partition P_2014_11_02, partition P_MAXIMUM) update indexes;
Table altered.
SQL> select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION;
TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ---------- --------------- -------- --------------- CHILD P_FIRST ENABLED ADVANCED CHILD P_2014_11_01 ENABLED ADVANCED CHILD P_2014_11_02 ENABLED BASIC CHILD P_MAXIMUM ENABLED BASIC PARENT P_FIRST ENABLED ADVANCED PARENT P_2014_11_01 ENABLED ADVANCED PARENT P_2014_11_02 ENABLED ADVANCED PARENT P_MAXIMUM ENABLED ADVANCED 8 rows selected.

Workaround:

set “_force_oltp_compress”=true in session level or system level.

Bug:

Bug 20081931 has been raised by Oracle Support to Oracle Development

Bug 20081931 – CHILD TABLE DOESN’T INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLIT PARTITION

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