Automatic management of range interval tables

Abstract

Range interval tables can save disk space.

If for instance, you wish to keep just a week’s worth of transactions, in order to save disk space perhaps, then it is quite straightforward to use a range interval fragmented table, and whenever you wish you can almost instantly purge any unwanted data by using the DROP or DETACH clause in the ALTER FRAGMENT statement. In any release prior to v12.10 this would have to done manually, as it were (albeit via a cron job perhaps), but now it can be managed entirely by the instance with the ‘rolling window’ functionality, as described in this article.

Content

The following SQL will create a range interval fragmented table –

Copy to Clipboard
Syntax HighlighterCREATE TABLE relocate
  (
    asset CHAR(30),
    old_loc CHAR(12),
    new_loc CHAR(12),
    login_name CHAR(30),
    the_date DATETIME YEAR TO MINUTE
  )
  FRAGMENT BY RANGE(the_date) INTERVAL(INTERVAL(1 00:00:00) DAY(9) TO SECOND)
    STORE IN (data01, data02, data03)
    PARTITION p1 VALUES

Any row inserted with a value in column ‘end-date’ prior to 30/10/2014 will go into fragment (aka partition) p1, if ‘end_date’ is 30/10/2014 it will go in p2, and new fragments will be automatically created if a row is inserted with a value in end_date which is outside the range of any current fragment. All new fragment will be created in a round robin fashion in the dbspaces referred to in the “STORE IN” clause.

If you only want to keep a maximum of one week’s worth of data (for instance) then simply add “ROLLING ( 7 FRAGMENTS ) DETACH” to the CREATE statement, as highlighted below –

Copy to Clipboard
Syntax HighlighterCREATE TABLE relocate
  (
    asset CHAR(30),
    old_loc CHAR(12),
    new_loc CHAR(12),
    login_name CHAR(30),
    the_date DATETIME YEAR TO MINUTE
  )
  FRAGMENT BY RANGE(the_date) INTERVAL(INTERVAL(1 00:00:00) DAY(9) TO SECOND)
    ROLLING ( 7 FRAGMENTS ) DETACH
    STORE IN (data01, data02, data03)
    PARTITION p1 VALUES

The internal task scheduler (easily managed via OAT) includes a task named ‘purge_tables’ which by default is enabled and runs daily. This process will check all rolling window tables and ensure that they stay within their limits, so if this table has more than 7 fragments it will detach as many as necessary, in ascending order of ‘evalpos’ (the VALUES clause of the partition definition). DETACH will cause each of those fragments to be detached from relocate, and converted to a new table per fragment, named after the table and the fragment’s interval. If you don’t want to keep the data simply use DISCARD instead of DETACH in the ROLLING clause.

Additionally one can add an overall size limit – replace the ROLLING clause with the following, for instance –

Copy to Clipboard
Syntax HighlighterROLLING ( 7 FRAGMENTS ) LIMIT TO 100MiB DETACH INTERVAL FIRST

Each time ‘purge_tables’ is run, if either of these two limits (7 fragments or 100Mb in total, including any indexes) is exceeded it will detach as many fragments as it needs to in order to keep the table within the limit in question, in ascending order of ‘evalpos’. If you don’t mind which fragment is detached when the LIMIT clause triggers the purging you can replace INTERVAL FIRST with ANY.

You can use the ALTER FRAGMENT statement to modify, enable or disable any of the rolling window features e.g.

Copy to Clipboard
Syntax HighlighterALTER FRAGMENT ON TABLE relocate 
               MODIFY INTERVAL ROLLING ( 14 FRAGMENTS) DISCARD

Note that user defined fragments are not included in this limit, so given the definition of the table in this article fragments p1 and p2 will not be purged automatically – but that can of course be done manually as and when required.

Caveats

This is only available for range interval fragmented tables – if the table is fragmented by the first character of a column, for instance, it cannot have a rolling window.

Any index on the table must have the same range interval fragmentation.

A rolling window cannot be implemented on a table with ROWID virtual columns, or a column defined as the primary key of a referential constraint.

If you use the DETACH functionality you will still have some space management to do yourself.

Conclusion

This takes care of all the management of a range interval fragmented table, and is particularly useful if you want to change the interval definition e.g. when your business is a bit quieter you may decide to keep the data for a bit longer, and return to the previous policy when things get busier again – you only have to run the ALTER TABLE statement, and the next time the ‘purge_table’ task is executed it will be aware of the new structure.

But essentially managing such a table can now be done to your specification by the instance, rather than being reliant on SQL which is built and run by a shell script (for instance) which in turn is reliant on the cron daemon, Windows Task Scheduler or such.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.