In-Database Archiving: Partitioning (Part I)

Today we come across with a big challenge: storing vast quantities of data for the lowest cost possible. The problem with all that data is that it will eventually become historical one day. That historical data may be subject to “archiving tiering”, partitioning, compression, etc but the application still can see and query old data in all “tiers”, all partitions, even they are not necessary to handle for the current scope of the application.

In-Database Archiving: How to enable it

Oracle 12c tried to solve this issue with a new feature called “In-Database Archiving”, which allows historical data to be marked and not shown based on session parameter. It is a simple column called ORA_ARCHIVE_STATE added to table when you enable “ROW ARCHIVAL” for the table.

A brief demonstration will be:

Create a table, enable “ROW ARCHIVAL” and query dba_tab_cols to check the new column. Please note that hidden column SYS_NC00002$ it is used to track what are the rows subject to ILM row archival policy:


SQL> create table inarch_t2 as select round(dbms_random.value(1,1),0) N1 from dual connect by level < 10;

SQL> alter table inarch_t2 row archival; 

QL> select column_name, virtual_column, segment_column_id, internal_column_id from dba_tab_cols where table_name = UPPER('inarch_t2');

COLUMN_NAME	   	  VIR  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------------ --- ----------------- ------------------
ORA_ARCHIVE_STATE  NO		       3		  3
SYS_NC00002$	   NO		       2		  2 
N1		   		   NO		       1		  1

SQL> UPDATE inarch_t2 SET ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) WHERE N1=1 and rownum < 5;

SQL> select ORA_ARCHIVE_STATE, SYS_NC00002$, N1 from inarch_t2;

OR SYS_NC0000	      N1
-- ---------- ----------
1  01		       1
1  01		       1
1  01		       1
1  01		       1
0		     	   1
0		    	   1
0		    	   1
0		      	   1
0		       	   1

As you read, it is very simple to enable “ROW ARCHIVAL” in table and to set some rows to “archived” using the DBMS_ILM.ARCHIVESTATENAME().

In-Database Archiving: Partioning based on ORA_ARCHIVE_STATE

Apart from that, the first thing that comes to my mind is the use of List Partitioning using ORA_ARCHIVE_STATE. The ideia behind it is to create two partitions based on ORA_ARCHIVE_STATE value. That will allow us to “move” data that we consider for historical purposes to another partition (or if you want to another tablespace) and still can decide from a session prespective that application can or cannot query the data on partition with historical data.

First we will create a table containing 51 rows of random Euro Millions results gently generated by Oracle 12c for us:

SQL> create table euromillions (N1 number, N2 number, N3 number, N4 number, N5 number, E1 number, E2 number) 
		partition by LIST (ORA_ARCHIVE_STATE) 
			(PARTITION fresh VALUES (0), 
				PARTITION old VALUES(1)) 
		ENABLE ROW MOVEMENT ROW ARCHIVAL;


Table created.


SQL> select COLUMN_NAME, COLUMN_POSITION from DBA_PART_KEY_COLUMNS where owner = 'LCMARQUES' and NAME='EUROMILLIONS';

COLUMN_NAME	     COLUMN_POSITION
-------------------- ---------------
ORA_ARCHIVE_STATE		   1


SQL> insert into euromillions
  2  select round(dbms_random.value(0,50),0), round(dbms_random.value(0,50),0), round(dbms_random.value(0,50),0),
  3  round(dbms_random.value(0,50),0), round(dbms_random.value(0,50),0), round(dbms_random.value(0,10),0), round(dbms_random.value(0,10),0) from dual connect by level < 52;

51 rows created.

We have now a table a table called EUROMILLIONS with 51 rows and 7 columns plus ORA_ARCHIVE_STATE that was used to create two partitions called fresh and old. As you expect after the INSERT only fresh partition contains data:


SQL> select count(1) from euromillions partition (old);

  COUNT(1)
----------
	 0

SQL> select ORA_ARCHIVE_STATE, count(1) from euromillions group by ORA_ARCHIVE_STATE;

ORA_A	COUNT(1)
----- ----------
0	      51


As expected we don’t have any data on old partition, since all the values are non-archived data and that means that In-Database Archiving column ORA_ARCHIVE_STATE is 0 for all the rows. Let’s now make some random euro millions results (where the first number to come out is between 1 and 10) using a ILM feature for In-Database Archiving:


SQL> UPDATE euromillions SET ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) WHERE N1>1 and N1<10;

7 rows updated.


Seven results are now marked as archived data and we expect that the data archived go to “old” partition as result of ORA_ARCHIVE_STATE update. But remember, by default the archived data (with ORA_ARCHIVE_STATE=1) is not visible you may need to set visibility at session level to actually count the values on partition:


SQL> select count(1) from euromillions partition (old);

  COUNT(1)
----------
	 0

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> select count(1) from euromillions partition (old);

  COUNT(1)
----------
	 7

We have now the 7 archived rows in a partition called old that are only possible to see and query if you set the the “ROW ARCHIVAL VISIBILITY=ALL” at session level, since the default is “ACTIVE“.

You can now use this technique to handle your historical data in a more smarter way. Next post i will write about set ILM policies and ADO to In-Archiving feature.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s