Home » RDBMS Server » Performance Tuning » In-database archival (Oracle 12C)
In-database archival [message #658037] Thu, 01 December 2016 03:35 Go to next message
srinivas.k2005
Messages: 371
Registered: August 2006
Senior Member
Hello,

I have come across the concept "in database archiving oracle 12c". Does it really helps to improve the performance of the queries? Please let me know the limitations.

I tested locally and it has not improved anything in the size the tables and nor it has helped to improve the performance of the queries.

Please advise.

Regards,
SRK
Re: In-database archival [message #658038 is a reply to message #658037] Thu, 01 December 2016 03:38 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
This is not a performance feature. It just adds a filter to all your queries, which (if you have not pre-pended the column to your indexes) may slow things down. For example:
orclz>
orclz> set autot on exp
orclz> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 1006289799

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------

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

orclz> alter table emp row archival;

Table altered.

orclz> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2002 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |  2002 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 | 28028 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

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

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

orclz>




Re: In-database archival [message #658043 is a reply to message #658038] Thu, 01 December 2016 05:11 Go to previous message
srinivas.k2005
Messages: 371
Registered: August 2006
Senior Member
Thanks a lot John:)
Previous Topic: java.sql.SQLTimeoutException: ORA-01013
Next Topic: Materlized View
Goto Forum:
  


Current Time: Tue Oct 16 03:37:35 CDT 2018