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:
12c: In-Database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Solution To Performance Issue With In-database Archiving
—————————————————————————————-