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:

--- DBATOOL has 8 rows

SQL> select * from dbatool;

       EMP
----------
	 1
	 2
	 3
	 4
	 4
	 3
	 3
	 3

8 rows selected.


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

SQL> explain plan for select * from dbatool;

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2022247455

-----------------------------------------------------------------------------
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	    |	  8 |	104 |	  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DBATOOL |	  8 |	104 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic statistics used: dynamic sampling (level=2)

12 rows selected.

Enable archiving of that table.

SQL> alter table dbatool row archival;

Table altered.

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

SQL> ;
  1* select ora_archive_state,emp from dbatool
SQL> /

ORA_	    EMP
---- ----------
0	      1
0	      2
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

8 rows selected.

Lets archive the rows whose EMP is 1 or 2 .

SQL> 
SQL> update dbatool set ora_archive_state=dbms_ilm.archivestatename(1) where emp in (1,2);

2 rows updated.

SQL> commit;

Commit complete

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

SQL> select ora_archive_state,emp from dbatool;

ORA_	    EMP
---- ----------
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

6 rows selected.

SQL> select count(*) from dbatool;

  COUNT(*)
----------
	 6

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

SQL> explain plan for select * from dbatool;



Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2022247455

-----------------------------------------------------------------------------
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	    |	  6 | 12090 |	  2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DBATOOL |	  6 | 12090 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBATOOL"."ORA_ARCHIVE_STATE"='0')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

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

SQL> alter session set ROW ARCHIVAL VISIBILITY  = all;

Session altered.

SQL> select ora_archive_state,emp from dbatool;

ORA_	    EMP
---- ----------
1	      1
1	      2
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

8 rows selected.

For disabling archiving:

alter table DBATOOL no row archival;
Table altered.

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.

SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:01.01

16:34:40 SQL> explain plan for
16:34:43   2  select count(*) from test2 where owner='SYS';

Explained.

Elapsed: 00:00:00.03
16:34:46 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 225723219

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    66 |   258   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    66 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_INDX |     1 |    66 |   258   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OWNER"='SYS')

14 rows selected.

Elapsed: 00:00:00.23

Now lets enable row archival, and check the performance.

AFTER ENABLING ROW ARCHIVAL:

16:36:11 SQL> alter table test2 row archival;

 

SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:04.16
16:37:09 SQL> explain plan for
16:37:16   2  select count(*) from test2 where owner='SYS';

Explained.

Elapsed: 00:00:00.00
16:37:19 SQL>
16:37:25 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 147238325

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  2068 |   367   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |  2068 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST2 |     1 |  2068 |   367   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("OWNER"='SYS' AND "TEST2"."ORA_ARCHIVE_STATE"='0')

14 rows selected.

Elapsed: 00:00:00.03
16:37:28 SQL>

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.

SQL> drop index TEST_INDX;

Index dropped.

Elapsed: 00:00:00.28


16:39:16 SQL> create index TEST_INDX on TEST2(OWNER,ORA_ARCHIVE_STATE) parallel 8;

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname =>'DBATEST',tabname =>'TEST2',cascade => true, method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

PL/SQL procedure successfully completed.


16:41:51 SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:01.06
16:41:55 SQL>  explain plan for
16:42:00   2  select count(*) from test2 where owner='SYS';

Explained.

Elapsed: 00:00:00.00
16:42:01 SQL>  select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 225723219

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     8 |  1366   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_INDX |   503K|  3932K|  1366   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OWNER"='SYS' AND "TEST2"."ORA_ARCHIVE_STATE"='0')

14 rows selected.

Elapsed: 00:00:00.03

Now query is running fine with proper index