HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Ageing of data - avoid flat consideration of data in time (and ORA-39726)
DATA ARCHITECTURE
04-Sep-2008
Once you realizes to store historical data, you have to solve their ageing. You should not do it at very start, anyway difficulties of the solution will increase aside amount of your data and complexity of your system. Therefore it is reasonable solve ageing from the very start.

This article is aimed to describe the simpliest three steps of ageing:

  • partitioning - is more prerequisity of following steps than the method itself. Partitioning of all tables containing histotical date is must.
  • compression - it is as easy and efficient way that it is strange it is not standardly used. In most infrastructures it is more efficient to read data from compressed partitions than from uncoplressed. Compression of partitions saves more than 50% of space of data segments and approximatelly 40% of both data and index segments.
  • read only tablespaces - save requirements to backup data. They could be stored on less safe disks without expansive mirroring methods.

  • Myths about ageing
    One of very spread myths about compression and usage of read only tablespaces is ostensible impossibility to add columns into such tables. Anyway it is not true in Oracle 10g+. There is misleading error message:

    SQL> alter table tt1 drop column x1; alter table tt1 drop column x1 * ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables

    but it appears when you try to drop column. You can add columns into tables with both compressed partitions and partitions in read only tablespaces. The process rocks just when you try to add column with default value. It fails with ORA-39726 too. If you have to divide action into six steps:

  • Add column without default option
  • Modify column - set default option
  • Set all the read-only tablespaces containing partitions as read-write
  • Update column - set default value
  • Set all the "read-only" tablespaces containing partitions back to read-only
  • Modify column - set not null option (NO VALIDATE)
  • Anyway last four steps are necessary just for not null columns. It is a tax of not null strategy. In this case it is not much expensive.
    Good news - New feature of Oracle 11g alows to add columns even with default and not null options. It does not require to physical changes of old records so it is much faster than all the workarounds required in 10g.

    alter table tt1 add x1 integer;
    alter table tt1 modify x1  default 1 ;
    alter tablespace TS_RO read write;
    update tt1 set x1=1;
    commit;
    alter tablespace TS_RO read only;
    alter table tt1 modify x1 not null NO VALIDATE;


    Anyway drop column is impossible cause of compressed partitions even in 11g. Differently from 10g you can use following way:

  • Set column(s) UNUSED.
  • Set read only tablespaces to READ WRITE.
  • Drop UNUSED columns.
  • Set read only tablespaces back to READ ONLY.
  • alter table tt1 set unused column a;
    alter tablespace usero read write; --read only tablespaces
    alter table tt1 drop unused columns;
    alter tablespace usero read only; --read only tablespaces


    Partitioning driven by ageing requirement There are two basic types of history by the partitioning view:
  • Snapshots - contains date value of snapshot or date of transaction used as a partitioning key. It could be either list or range partitioning.
  • Bands - contains two relevant date values defining start and end of validity (valid from, valid to). It is much more complex and it is detaily described in separate article.
  • See: Ageing of data with band validity (SCD, History tables)


    Compression and read only tablespaces There are two basic ways of ageing that way:
  • Oracle driven move - segments are moved between tablespaces
    • 1st step - Move partition compress
    • 2nd step - Move partition into target placed tablespace
    • 3rd step - Set tablespace read only
  • FS driven move - the second move of tablespace is solved on FS level
    • 1st step - Move partition compress into tablespace dedicated to certain time band
    • 2nd step - Move files of tablespace
    • 3rd step - Set tablespace read only
    I recommend the first way because it is better manageable. Ageing process could be mostly independent on ETL processes filling the table.

    Ludek Bob Jankovsky
    All Right Reserved © 2007, Designed by Bob Jankovsky