X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

  • ILM
    September 30, 2016

In-Database Archiving – An ILM Feature You’ve Probably Not Heard About

Gregg Christman
Product Manager

While my Blogs
typically cover the features associated with Advanced Compression, I thought it
would be useful to discuss In-Database Archiving, an Information Lifecycle
Management (ILM) feature that is new in Oracle Database 12c and not well known.


In-Database
Archiving allows users, and applications, to set the archive state for individual
rows. Rows that have been marked as archived stay where they are, but are not
visible unless the session is enabled to see archived data. To manage
In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table
using either CREATE TABLE or ALTER TABLE, for example:

CREATE TABLE
residents (

id NUMBER,

street VARCHAR2(50),

CONSTRAINT tbb1_pk PRIMARY KEY (id))

ROW ARCHIVAL;

Enabling
in-database archiving causes the addition of a system generated hidden column
called ORA_ARCHIVE_STATE.

SELECT column_id,

column_name,

data_type,

data_length,

hidden_column

FROM user_tab_cols

WHERE table_name = 'RESIDENTS'

ORDER BY
column_id;

COLUMN_ID  COLUMN_NAME        DATA_TYPE  DATA_LENGTH HID

----------
-----------        ---------  ----------- ---

1
         ID                 NUMBER         22      NO

2
         STREET             VARCHAR2       50      NO

           ORA_ARCHIVE_STATE  VARCHAR2       4000    YES

By default, this column is populated with the
value '0' for each row. When the value for ORA_ARCHIVE_STATE is set to ‘0’ then
that row is visible to applications, meaning that the row is active and visible
via a standard query. In the example below, all four records have
ORA_ARCHIVE_STATE set to ‘0’.

SELECT id,

street,

FROM residents;

ID         STREET

----------
--------------------

1
         Maple St

2
         Main St

3
         Elm St

4
         Poplar St


To make rows
invisible to applications, update the ORA_ARCHIVE_STATE system generated hidden
column with the value '1'. In the example below, two records (ID #2 and 4)
have been updated and have had their ORA_ARCHIVE_STATE set to ‘1’, making those
records invisible to applications, so now the same query would return the
following results:

SELECT id,

street,

FROM residents;

ID         STREET

----------
--------------------

1
         Maple St

3
         Elm St

Note that the
ORA_ARCHIVE_STATE column can be set to any string value other than '0' to
archive the data, the DBMS_ILM package uses the following constants:

*
ARCHIVE_STATE_ACTIVE='0'

*
ARCHIVE_STATE_ARCHIVED='1

Optionally, you
can specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session
parameter to make archived rows visible or invisible for the session. If you specify ACTIVE, then the database will
consider only active rows when performing queries on tables that are enabled
for row archival. This is the default.

ALTER SESSION SET
ROW ARCHIVAL VISIBILITY = ACTIVE;

If you specify
ALL, then the database will consider all rows when performing queries on tables
that are enabled for row archival.

ALTER SESSION SET
ROW ARCHIVAL VISIBILITY = ALL;

As seen above,
In-Database Archiving adds a hidden column to the table to specify the
visibility of each row. For every query and DML, Oracle then injects a
predicate to limit the query/DML to only the visible rows. This added predicate
could affect the optimizer, indexes, etc. just like any other predicate.
Testing with your own data and applications is the best way to decide whether
any possible overhead is acceptable for the benefit provided.

For those who
attended my Advanced Compression Insights session at Oracle Open World 2016
(CON6410), I have uploaded my slides into the OOW session repository – and
thank you for attending my session!

But for now the
database storage optimization adventure continues, in my next blog we will
discuss Advanced Compression with RMAN.

Join the discussion

Comments ( 1 )
  • Andrew Monday, November 18, 2019
    Thanks for the blog. Do you know if the feature is available in SE? Since the documentation is in the VLDB and partitioning book I am skeptical it is in SE but I cannot find it in a feature matrix.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.