CHILD TABLE DOESN’T INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLITTING PARENT 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 fo BASIC’ by default.

The test case below 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
 2 (
 3 pk_p_id number primary key ,
 4 P_Date DATE,
 5 P_cost NUMBER
 6 ) compress for oltp
 7 PARTITION BY RANGE (P_Date)
 8 (PARTITION P_First VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
 9 PARTITION P_2014_11_01 VALUES LESS THAN (TO_DATE('02-11-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
 10 PARTITION P_MAXIMUM VALUES LESS THAN (MAXVALUE) COMPRESS for OLTP
 11 );

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

Advertisements
This entry was posted in oracle advanced compression, Oracle DBA Practice, Partitioning 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