In my earlier post, I had demonstarted the performance issue that could arise with in-database archiving. In this post, I am going to provide the solution to the same (as suggested by John Watson).
– 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;
– Let’s create a concatenated index on id, ora_archive_state columns
PDB1@CDB1> create index hr.emp_idx on hr.emp(id, ora_archive_state);
– Let’s try to add a duplicate record for id = 3 - fails
PDB1@CDB1> 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_C009861) violated
– Let’s issue various queries and check their execution plans
— It can be seen that all the queries are executed by accessing only the index on id and ora_archive_state (emp_idx)
PDB1@CDB1> select count(*) from hr.emp; Execution Plan ---------------------------------------------------------- Plan hash value: 660937672 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| EMP_IDX | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------- PDB1@CDB1> select count(*) from hr.emp where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) PDB1@CDB1>select count(*) from hr.emp where id <3; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 2 | 6 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<3) PDB1@CDB1> select count(*) from hr.emp where id between 1 and 3; Execution Plan ---------------------------------------------------------- Plan hash value: 2081018037 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| EMP_IDX | 3 | 9 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=3) Conclusion:
In case in-database archiving is enabled for a table, indexes on various columns should be appended by column ORA_ARCHIVE_STATE to ensure performance.
I hope this post was useful.
Your comments and suggestions are always welcome!
References:
http://www.orafaq.com/node/2824
———————————————————————————————-
Related links:
12c: In-database Archiving
12c: Improve Backup Performance Using In-database Archiving
12c: Performance Issue With In-database Archiving