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.
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:
CONSTRAINT tbb1_pk PRIMARY KEY (id))
in-database archiving causes the addition of a system generated hidden column
WHERE table_name = 'RESIDENTS'
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HID
----------- --------- ----------- ---
ID NUMBER 22 NO
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’.
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
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:
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.