Oracle in-database archiving and invisible columns in 12c

In Database tuning, DBA, Oracle database, Oracle internals, SQL on September 30, 2013 at 01:15

“The true mystery of the world is the visible, not the invisible.” Oscar Wilde


In-Database archiving is new to Oracle 12c. The idea behind it is to make rows invisible. This is very similar to the invisible column concept.

The two features, combined together, can make big data in the table look not that big to the application.

Check the demonstration from orafaq to see how it works. It is simple and rather trivial. Another example is given by Contract Oracle Limited.

What is really interesting is John Watson’s comment and example at the very end of the orafaq article. He shows the need for filters that cannot be accomplished with access. John suggest that “all indexes need to include the ora_archive_state column, perhaps appended to primary and unique key indexes and prepended to other indexes. If you don’t do this, you will find that many operations that could previously be completed without touching the table will require table scans”.

The AMIS Techonology blog pays attention to the very same thing:

The Oracle Documentations says the following: “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.”

The AMIS Technology Blog says: Note the emphasis on ‘without compromising performance’. It would seem that such in line archived records are skipped in a smart way during full table scans and other records retrieval operations.

Yong Woo, shows in his short note 12c: In-Database Archival …good, but not good with an example how a ‘TABLE ACCESS BY INDEX ROWID’ can be brought to the execution plan because of the new pseudo column.

What I would like to show, is in a way also the opposite, that performance can be even better with Oracle in-database archiving. I have a table called clients and visibility is set to ACTIVE. Almost all rows of the table are “invisible”. Check how bytes and cost increase, once I set the visibility to ALL. And of course, the full table scan!

SQL> select * from clients;


SQL> alter session set row archival visibility = all;

Session altered.

SQL> select * from clients;


It is interesting to note that virtual columns can be made invisible:

SQL> alter table clients add (vip_status number 
     generated always as (power(status,status)) virtual);

Table altered.

SQL> alter table clients modify (vip_status invisible);

Table altered.

INVISIBLE columns are not supported in external tables, cluster tables, or temporary tables and you cannot make a system-generated hidden column visible.

You can not yet make a table invisible. If you start marking all columns from the table as invisible, at the very end you will get:

SQL> alter table clients modify (client_id invisible);
alter table clients modify (client_id invisible)
ERROR at line 1:
ORA-54039: table must have at least one column that is not invisible

On a final note, I wonder what developers will do if they cannot insert a row into a table if a DBA adds an invisible column without a default value declared as NOT NULL 🙂

  1. Hi Julian,

    does the feature indeed help to “store more data for a longer period of time within a single database, without compromising application performance”? Taking a look at a corresponding block dump I don’t see much difference to the manual addition of an additional archive flag column. I understand that the feature makes the administration much simpler – but I do not see much performance improvement: if I have a big table with a lot of archived rows a FTS will still have to read all the blocks and filter the archived rows. It would be nice if “such in line archived records are skipped in a smart way during full table scans and other records retrieval operations” – but I don’t know how this could be done. In addition I did not find a description of special compression features to compress only archived rows (of course I can compress the complete table – but since compression and updates don’t mix very well that’s something I would like to restrict on archived rows).

    Maybe I am missing some important point but my impression is that the feature is not really performance related.

  2. I agree with you that such in-line archived records should be skipped in a smart way during full table scan.Probably the same way data is skipped via storage indexes in Exadata.

  3. […] In-Database Archiving enables us to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. To manage In-Database Archiving for a table, we must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Check my old blog post. […]

  4. Howdy I am so thrilled I found your blog page, I really found you
    by mistake, while I was browsing on Yahoo for something else, Anyways I am here now
    and would just like to say many thanks for a fantastic post and
    a all round thrilling blog (I also love the theme/design), I
    don’t have time to look over it all at the
    moment but I have bookmarked it and also added your RSS feeds, so when I
    have time I will be back to read much more,
    Please do keep up the great work.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: