ORA-02158: invalid CREATE INDEX option

It seems Oracle Advanced Compression Feature does not apply to IOT tables.

Tried to apply Oracle Advanced Compression Feature onto IOT tables in 12.1.0.2, then got this error.

SQL> CREATE TABLE TEST_IOT_TBL
          ( id   number,
            sex  char(1),
           name  varchar2(20),
           CONSTRAINT TEST_IOT_TBL_PK PRIMARY KEY (id,sex ) ENABLE
         ) ORGANIZATION INDEX ;

Table created.

SQL> insert into TEST_IOT_TBL values ( 1,'M','test1');

1 row created.

SQL> insert into TEST_IOT_TBL values (2,'M','test2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table TEST_IOT_TBL move compress for all operations;
 alter table TEST_IOT_TBL move compress for all operations
 *
ERROR at line 1:
ORA-02158: invalid CREATE INDEX option

ORA-14451: unsupported feature with temporary table

Temporary table cannot be compressed by using Oracle Advanced Compression.

It looks like temporary table can not be compressed by using Oracle Advanced Compression Feature in 12.1.0.2.

SQL> alter session set current_schema=testuser;

SQL> CREATE GLOBAL TEMPORARY TABLE TEST_TBL 
        ( id number, name varchar(20), 
          CONSTRAINT TEST_TBL_PK PRIMARY KEY (id) ENABLE 
        ) ON COMMIT DELETE ROWS;

Table created.

SQL> insert into TEST_TBL values ( 1,'testuser');

1 row created.

SQL> select * from TEST_TBL;

ID NAME
---------- --------------------
 1 testuser

SQL> alter table TEST_TBL move compress for all operations;
 alter table TEST_TBL move compress for all operations
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> commit;

Commit complete.

SQL> select * from TEST_TBL;

no rows selected

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