Category: Oracle

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