Category: oracle advanced compression

Oracle Fails to Drop Unused Columns

When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped. 

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

Further investigation shows the table is created with compression :

SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';

OWNER    TABLE_NAME   COMPRESS  COMPRESS_FOR
-------- ------------ --------- -------------
TESTER    TRANSACTIONS ENABLED    ADVANCED

Change table to uncompressing status without moving data:

SQL>  alter table TRANSACTIONS NOCOMPRESS;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Have to move table with nocompress option, then drop unused columns successfully.

SQL> alter table TRANSACTIONS move nocompress;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

no rows selected
Advertisement

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