This content has been marked as final. Show 2 replies
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.
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
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>>;
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.