Oracle Database Discussions

You start by saying you're only going to keep 15 days of data, then in question 2 you imply that you want to keep the current 3 years as well.

If you only need to keep 15 days of data your best strategy should take about 5 minutes to run:

Create an empty interval partitioned table with one partition for (say) data before 6th Jan, with local indexing

Exchange your current table with the bottom partition

Wait 15 days

You will need to work out a few refinements to method to minimise side effects - but "stop time" for the application will be very small.

The biggest problem with your requirement is that Oracle isn't friendly about dropping the partition that is on the boundary between the RANGE bit and the INTERVAL bit - the whole "interval partitioning" thing really needs a lot of work to make it DBA-friendly, it covers a few points and leaves many more that still need site-specific thinking.

Regards

Jonathan Lewis

Thank You Jonathan.

The biggest problem with your requirement is that Oracle isn't friendly about dropping the partition that is on the boundary between the RANGE bit and the INTERVAL bit - the whole "interval partitioning"

But , after the 15th day, I will not have any problem dropping the partitons back from the 13th day and backwards. Right ? Its only the boundary (I assume the 14th day's partiton) I will have trouble with. Right ?

I'm not quite sure what you have in mind with your comment, but it's easy enough to check whether what you have in mind will work.

Here are a couple of useful blog posts from Harald van Breederode describing the issues and commenting on strategies to address them.

http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

https://prutser.wordpress.com/2010/07/26/dropping-interval-partitions-revisited/

Regards

Jonathan Lewis

Question1. Any idea how long it will take to do Exchange partitiong using Online Redefinition ? Just a rough estimate would do.

Question2. In the existing table, we have data from the years 2013, 2014 and 2015. When creating the above mentioned partitioned table, Is there a way we could split the base partition for years 2013,2014 and 2015 ? 

Question3. After the Online redefinition , I have to manually create the constraints and Indexes . Right ?

Any suggestions/reccomendations are welcome

#1 - you don't use 'Online Redefinition' when you use exchange partition. The exchange will take a few milliseconds.

#2 - How can the table have data from 2013, 2014 and 2015 if you only keep data for 15 days?

We are required by the law to keep the data only for 15 days

The ONLY way you can 'split' data is by physically moving the data. Once that BIG history partition is more than 15 days old just query out the data for 2013 and put it somewhere else.

If you still want it in the same table you will need to use SPLIT PARTITION.

#3 - there is no 'redefinition' when you use exchange partition.

Thank rp0428, Jonathan

Question4.

For a table which is partitioned daily like the above one, what type of index is better : Local or Global ? This is an OLTP environment

Question5

So, Online redefinition is needed to only to deal with Indexes and Constraints ? ie. After the partition exchange, if you are going to manually rename the constraints and indexes, then Online redefinition is not needed. Right ?

For daily partitions, I gather that local indexes are better.

Quoting Anurag from

https://community.oracle.com/thread/2364044?tstart=0

" (when using Global partitions) If you try to drop older partitions then you have to

1) Rebuild the entire global indexes.

2) Drop the older partitions with update global index clause, which would be much slower.  "

What about monthly partitions ?

Should I create global or local indexes for monthly partitions?

For any partitioning, if you only require local indexes then that is best.

But if you need global indexes, then you need global indexes.

It is not driven by any particular partitioning scheme but by your data and how you access your data.

The drivers are normally either performance (because your queries are not restricted to a single or small number of partitions) or data quality (because your unique/primary constraint does not include the partition key).

What about monthly partitions ?
Should I create global or local indexes for monthly partitions?

RTFM and follow the guidelines in the docs. They tell you, step by step, what the considerations are for choosing the type of index.

https://docs.oracle.com/cd/E18283_01/server.112/e16541/partition.htm#i461446

Overview of Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

 If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2. 
 If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3. 
 If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4. 
 If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index. 

For more information about partitioned indexes and how to decide which type to use, refer to Chapter 6, "Using Partitioning in a Data Warehouse Environment" and Chapter 7, "Using Partitioning in an Online Transaction Processing Environment".