In my earlier article on in-database archiving, I had demonstrated the functionality of this new feature introduced in Oracle 12c. But this feature you could also give problems with performance as pointed out by John Watson (http://www.orafaq.com/node/2824).

Let’s create a table of employees wherein we would like to archive the records of the employees who have retired.

SQL>create table hr.emp (id  number primary key,
name  varchar2(30),
retired  char(1)) row archival;

insert into hr.emp values (1, 'ONE', 'N');
insert into hr.emp values (2, 'TWO', 'N');
insert into hr.emp values (3, 'THREE', 'Y');

SQL> col ora_archive_state for a3
select id, name, retired, ora_archive_state from hr.emp;

ID NAME                           R ORA
---------- ------------------------------ - ---
1 ONE                            N 0
2 TWO                            N 0
3 THREE                          Y 0

– Archive the records of retired employees

SQL> update hr.emp set ora_archive_state=1 where id = 3;

– Check that archived records are not visible now

SQL>  select id, name, retired, ora_archive_state from hr.emp;

ID NAME                           R ORA
---------- ------------------------------ - ---
1 ONE                            N 0
2 TWO                            N 0

– Let’s check that primary key constraint on the table won’t allow me to add an employee with same id as a retired employee

SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N');

insert into hr.emp values (3, 'ANOTHER THREE', 'N')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C009857) violated

- Let’s create another table which is copy of hr.emp but without row archival clause

SQL> create table hr.emp_copy as select * from hr.emp;
alter table hr.emp_copy add primary key(id);

– Verify that emp_copy does not contain column ora_archive_state

SQL> select id, name, retired, ora_archive_state from hr.emp_copy;
select id, name, retired, ora_archive_state from hr.emp_copy
*
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

– Let’s issue the same query against the two tables and check the execution plan
— In case of table without row archival, the query is executed by accessing the index only

SQL> set autot trace exp
     select count(*) from hr.emp_copy where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2028575012

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C009854 |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("ID"=1)

– In case of table with row archival, Table Access by RoWID is used in addition to Index Unique Scan , the reason being the filter
“ORA_ARCHIVE_STATE”=’0′ used while executing the query

SQL> select count(*) from hr.emp where id=1
  2  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1746399475

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |  2015 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |  2015 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     1 |  2015 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C009859 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - filter("EMP"."ORA_ARCHIVE_STATE"='0')
   3 - access("ID"=1)

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

– We can try to resolve this problem by appending column ORA_ARCHIVE_STATE to the primary key 

SQL> alter table hr.emp drop primary key;

alter table hr.emp add primary key(id, ora_archive_state);

– Let’s check that now the query is executed using index access only

SQL> select count(*) from hr.emp ;

Execution Plan
----------------------------------------------------------
Plan hash value: 907615972

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |  2002 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |             |     1 |  2002 |            |          |
|*  2 |   INDEX FULL SCAN| SYS_C009860 |     2 |  4004 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("EMP"."ORA_ARCHIVE_STATE"='0')
       filter("EMP"."ORA_ARCHIVE_STATE"='0')

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

— But this could give rise to another problem related to constraint violation
– Let’s try to add an employee with id same as retired employee i.e. id = 3
— Oops.. It lets me do it!

SQL> insert into hr.emp values (3, 'ANOTHER THREE', 'N');

1 row created.

— Simply because primary key is composed of id and ora_archive_state and
ora_archive_state column is set to 0 (active/unarchived) by default

SQL> alter session set row archival visibility = all;

select id, ora_archive_state, name, retired from hr.emp;

ID ORA NAME                           R
---------- --- ------------------------------ -
1 0   ONE                            N
2 0   TWO                            N
3 1   THREE                          Y
3 0   ANOTHER THREE                  N

I still have to find the solution to this problem.

Thanks for your time!

References:

http://www.orafaq.com/node/2824

——————————————————————–

Related Links:

Home

Database 12c Index

12c: In-Database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Solution To Performance Issue With In-database Archiving

 

 

—————————————————————————————-