In oracle 12c a new feature called In-Database Archiving has been introduced. With this we can archive specific rows of a table as per our requirement. This is very helpful,when table contains lot of historical data and for full scan it is taking a lot of time.Using this we can archive the historical data.

We will see an example:

You can check the explain plan( Scanning all 8 rows)

Enable archiving of that table.

This will create one invisible column ORA_ARCHIVE_STATE:(0/1)

ORA_ARCHIVE_STATE:0 – Means that row is not archived
ORA_ARCHIVE_STATE:1 – Means that row is archived

Lets archive the rows whose EMP is 1 or 2 .

Now we can see the table has 6 rows and rest were archived.

If you check the explain ( It will scan only 6 rows, excluding the archival rows)

If you want to see the rows including archived rows at session level , then use

For disabling archiving:

This will drop that invisible column.

Performance Impact on sql queries and its fix:

Enable row archival  , can impact the performance of the sql queries. Lets see the below example:

BEFORE ENABLEING ROW ARCHIVAL:
Below query is taking proper index and completing in 1 second.

Now lets enable row archival, and check the performance.

AFTER ENABLING ROW ARCHIVAL:

 

Now after enabling row archival, same query is doing full scan on the table and taking around 4 seconds.
Because the row archival has added one virtual column , so the existing index not getting picked.

2 – filter(“OWNER”=’SYS’ AND “TEST2”.”ORA_ARCHIVE_STATE”=’0′)

To fix it, we need to drop the existing index and recreate the index by including the virtual column ORA_ARCHIVE_STATE.

Now query is running fine with proper index