This discussion is archived
3 Replies Latest reply: Apr 16, 2012 11:58 AM by acarrasco RSS

Compress database with ACO

acarrasco Newbie
Currently Being Moderated
Hi all,
In the coming soon weeks, we will want migrate from 10.2.0.5 to 11.2.0.3. We take the ACO license and we want apply compression to DWH database (aprox 4TB).
I tested this funcionality in a test enviroment with 2 tests:
1) IMPDP with direct path option. As you can imagine, the import is slowly. but I reduce about 20% of disk space. <<<---- WORK WELL
2) Execute an ALTER MOVE in two phases:
a) For non partitioned tables and after that rebuild non partitioned indexes
b) For partitioned tables and after that rebuild partitioned indexes

We need a minimal down time because, this database is in producction enviroment but when I try to compress or rebuild partitioned indexes, I got the following errors:

SYS@ORA11GR2> alter index PPP.PK_DIAL rebuild partition DIAL_110901 compress;
alter index PPP.PK_DIAL rebuild partition DIAL_110901 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first



SYS@ORA11GR2> alter index PPP.PK_DIAL rebuild;
alter index PPP.PK_DIAL rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole


We have several huge partitioned indexes, and I saw in metalink the next note: *Rebuild a partitioned index specifying compression raises Ora-28659 [ID 312843.1]* and I understand that I need drop and recreate all indexes.
My questions are:
1) We will use COMPRESS option at tablespace level. are there any recommendations about that? for example...use this option at partition or table level
2) which is the recommended method by us, since I have no clear, the reduction of time between the two methods previous.

Thanks a lot
Regards.

Edited by: 927890 on 16-abr-2012 7:48
  • 1. Re: Compress database with ACO
    damorgan Oracle ACE Director
    Currently Being Moderated
    The error message, it seems to me, is quite clear.

    First do an ALTER TABLE ... then ALTER PARTITION.
    SQL> CREATE TABLE sales (
      2  saleskey    NUMBER,
      3  quarter     NUMBER,
      4  product     NUMBER,
      5  salesperson NUMBER,
      6  amount      NUMBER(12,2),
      7  region      VARCHAR2(10))
      8  PARTITION BY LIST (region) (
      9  PARTITION northwest VALUES ('NORTHWEST'),
     10  PARTITION southwest VALUES ('SOUTHWEST'),
     11  PARTITION northeast VALUES ('NORTHEAST'),
     12  PARTITION southeast VALUES ('SOUTHEAST'),
     13  PARTITION western VALUES ('WESTERN'));
    
    Table created.
    
    SQL> alter table sales compress;
    
    Table altered.
    
    SQL> alter table sales move partition NORTHWEST compress;
    
    Table altered.
  • 2. Re: Compress database with ACO
    acarrasco Newbie
    Currently Being Moderated
    Hi again,
    Thanks for your quickly reply damorgan.
    All lf my partitioned tables, are already compressed:

    SQL> select distinct table_owner,compression, compress_for
    FROM dba_tab_partitions WHERE table_owner = 'PPP'
    order by 1;

    TABLE_OWNER COMPRESS COMPRESS_FOR
    ------------------------------ --------------- ------------
    PPP ENABLED BASIC

    1 rows selected.

    I see is that with MOVE clause the indexes are UNUSABLE or INVALID and then we need rebuild or recreate. In this point is in which I receive latest errors.
    As put into the official Oracle documentation, following the move command of the table or particions, it is necessary drop or rebuild indexes.

    - Oracle Documentation -

    Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt.

    We are comparing the size obtained after making the impdp and after making the partition and table MOVE, but is much lower for first (impdp).
    After IMPDP: we reclaim 20%
    After MOVE: we reclaim 3%

    I don´t know why??

    Thanks for all again.
  • 3. Re: Compress database with ACO
    acarrasco Newbie
    Currently Being Moderated
    Hi again,
    Thanks for your quickly reply damorgan.
    All lf my partitioned tables, are already compressed:

    SQL> select distinct table_owner,compression, compress_for
    FROM dba_tab_partitions WHERE table_owner = 'PPP'
    order by 1;

    TABLE_OWNER COMPRESS COMPRESS_FOR
    ------------------------------ --------------- ------------
    PPP ENABLED BASIC

    1 rows selected.

    I see is that with MOVE clause the indexes are UNUSABLE or INVALID and then we need rebuild or recreate. In this point is in which I receive latest errors.
    As put into the official Oracle documentation, following the move command of the table or particions, it is necessary drop or rebuild indexes.

    - Oracle Documentation -

    Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt.

    We are comparing the size obtained after making the impdp and after making the partition and table MOVE, but is much lower for first (impdp).
    After IMPDP: we reclaim 20%
    After MOVE: we reclaim 3%

    I don´t know why??

    Thanks for all again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points