This discussion is archived
2 Replies Latest reply: Sep 10, 2012 11:52 AM by 277293 RSS

enable OLTP compression on tables

user1779355 Newbie
Currently Being Moderated
Hi
I am trying to enable OLTP compression on tables and at tablespace level for the tables

Steps I am following are:
1. Move indexes to its own tablespace
2. enable OLTP compression at table level:
alter table table_name move compress for OLTP
3. Rebuild indexes
4. Issue I have is what to do with tables with LOB columns
ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); -- Is this correct?
5. alter tablespace data_tablespace default compress for OLTP;

I have a question, is the sequence of steps correct. For tables with LOB columns do we needto move lobindex to index tablespace. Beacuse lobsegment and lobindex are created in data tablespace?

thanks
  • 1. Re: enable OLTP compression on tables
    Marc Fielding Journeyer
    Currently Being Moderated
    Hello user1779355,

    OLTP compression isn't an Exadata-specific feature, so you might get a wider response in one of the general database forums.

    That having been said, I'll take a stab. The "tablespace level" OLTP compression you refer to is simply the default compression for new objects created in the tablespace. The tablespace itself isn't compressed, only individual segments inside it. That means that you don't need to move non-compressed objects out of the tablespace to realize the storage benefits of OLTP compression.

    However, the space you save by rebuilding tables with OLTP compression is only within the tablespace: the tablespace itself doesn't shrink in size. If you're looking to free up space for the database in general, consider moving all objects into a brand-new tablespace (compressing as you go), so that the old, large tablespace can be dropped and space freed up.

    Marc
  • 2. Re: enable OLTP compression on tables
    277293 Newbie
    Currently Being Moderated
    1. Move indexes to its own tablespace

    - This one you should do while rebuilding the indexes to save time( After Step2). To speed up, use parallel and nologging option while rebuilding them and after set index back to logging and noparallel.

    example : alter index <<index name>> rebuild parallel nologging tablespace <<new tablespace>>>

    2. enable OLTP compression at table level:
    alter table table_name move compress for OLTP

    or

    alter table table_name move compress for all operations;

    - This will invalidate all the indexes on table and you have to rebuild them again and you are doing in step 3 which is correct.

    3. Rebuild indexes
    use this to speed up things.
    alter index index_name rebuild online parallel nologging tablespace <<new tablespace>>;


    and then
    alter tablespace index_name noparallel logging;

    4. Issue I have is what to do with tables with LOB columns
    ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); -- Is this correct?

    - This is correct and this will move lob sys indexes to new tablespace as well.

    5. alter tablespace data_tablespace default compress for OLTP;

    All new objects that you will be creating in this tablespace will be compressed, not the existing one.



    Vineet

Legend

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