2 Replies Latest reply: Sep 10, 2012 1:52 PM by 277293 RSS

    enable OLTP compression on tables

    user1779355
      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
          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
            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