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
good one
good one. thanx.
Thanks for informations. It is looking like a little similar advantage “partitions” but better.
Yes, if you have a non-partitioned big table and want to archive the old date, without deleting them, Then this is very helpful
Nice feature… we can use it to hide data… let’s make some tests.
Good one.