Using Partitioning together, Advanced Compression and TotalRecall features can reduce the workload and, thus, work more efficiently.
Partitioning provides several ways of partitioning a table into multiple subtables and define multiple criteria partition from a Partition Key.
Advanced Compression provides multiple compression features, including OLTP compression. This feature is widely explained and tested in this same blog. Follow this link if you want to explore it deeper: http://studentsatoracle.blogspot.com/2010/12/advanced-compression-in-oracle-database_03.html.
Last, but no least, TotalRecall creates a flashback file to record activity on the table, so the user can see the value that had a record of that table on a certain day and time. In addition, the user can define the time of data retention and the quota of disk space used for it.
We'll test a "worst-case" scenario using all three features on a test table. We will create a table partitioned in two subtables, each one of them having a different compression type. Also, we'll set a flashback file so we can query the values of the table for a year.
But, we'll just focus on the most efficient way of combining the three features, we won't check any metrics like execution times or compression rate.
First we must do some thinking. Partitions of a table must be defined at the create table command while compression can be either defined at the creation of the table or later with the alter table command. We can alter a partitioned table, but due to limitations of partitioning, we can't alter the compression type of a subtable. So, we'll have to define the partitions and the compression type when we create the table.
So, let's create our test table:
create table atest ( c1 number(5), c2 varchar2(5), c3 varchar2(5), primary key (c1) validate)
partition by range(c1) (
partition atest_p1 values less than(6) compress,
partition atest_p2 values less than(maxvalue) compress for oltp
);
With this setting, we split the newly created table test in two subtables and the criteria for the partition is the range of values at the column c1. Any item with c1 lower than 6 will go to the subtable atest_p1, while the rest will go to atest_p2. In addition, we define the compression type for the subtables. Subtable atest_p1 has a basic compression, while atest_p2 has OLTP compression.
Then we fill it a little with some silly loop:
insert into atest
select rownum, (rownum*2)+2, (rownum*3)+3
select rownum, (rownum*2)+2, (rownum*3)+3
from dual connect by rownum <= 10;
Here comes the funny part. Once partitions, compression types and data are set, it's time to test the TotalRecall feature. First of all, we will need to create a new Tablespace:
create tablespace flasharch;
Now, we'll create and set the flashback file:
create flashback archive atest_fla tablespace flasharch quota 1M retention 1 year;
Once we create the flashback, we have to alter our test table to "link" them:
alter table atest flashback archive atest_fla;
And that's all there is to it. Now, let's give it a shot.
Make a simple update, changing the values inside our test table:
Make a simple update, changing the values inside our test table:
update atest set c2=c2+c3+12;
Then, execute a query refering to a moment before the update. To make things more difficult, let's ask only for the values of subtable atest_p2 (change the value inside the brackets for the time before the update if testing):
select * from atest partition (atest_p2) as of timestamp to_timestamp('DD-MMM-YY hh.mm.ss');
Keep in mind that once we alter the table to the flashback, we won't be able to perform some operations on that table, mostly alter table. We'll have to drop the flashback if we want to perform major changes to the "flashbacked" table. So, while this method gives all the benefits of the three features, also has the disadvantage of making the table somewhat static and unalterable. This means there must be some previous planning, because dismantling the settings may end in loss of information. Knowing all that, the advantages of using various powerful features integrated in the database are obvious.
Thanks for reading and feel free to leave any comments or questions.
