Partitioning or Backup Tables...?

Got this comment on the blog from Satish.

Satish is proposing (or forced) to create backup tables instead of using partitioning. I'm not sure you want to go there.

If you do, I think you will need to create views to show the data as a single entity, this means that you need to change the application to read from the views rather than from the original tables (or rename the tables first, then create views with the original names). You then want to compress the backup tables.

So you do a CTAS with a filter to create your backup tables from the original ones, add them to views and you are in business. You also just built the same as we do with partitions and how we would recommend doing database ILM strategy.

In other words, you are creating an ETL process that runs once a year (or at whatever interval). You will lose out on transparency, you will also lose out on maintenance, you will get a more complex system.

If you use partitioning, you will also get the benefits of faster stats gathering (in 11g - using incremental stats and the new Synopsis we use there) and potentially of data loading.

So the choice is to either make this transparent and get various other benefits - but pay some money. Or build it all yourself...

Look at these posts:
http://blogs.oracle.com/datawarehousing/2009/04/compressing_individual_partiti.html
http://blogs.oracle.com/datawarehousing/2009/08/partitioning_andorwith_exadata.html

Any who, one other resource to look at are the OTN Forums for questions like this.

Comments:

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
4
5
6
7
8
12
13
14
15
16
17
21
22
23
25
26
28
29
30
31
    
       
Today