Solved

Oracle 10g: Weekly to Daily Partition

Posted on 2011-02-14
12
1,539 Views
Last Modified: 2012-05-11
Hi Gurus,
 
We are using date partitioned tables, on a weekly basis, but now would like to move to daily partitions.
 
The question is; what is the best possible way to implement this big change for our huge Prod weekly partitioned tables?
 
Need some advice/guideline, thanks in advance.
(Oracle 10.2.04 on Solaris 10 8/07 s10x_u4wos_12b X86)
0
Comment
Question by:el123
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 251 total points
ID: 34889247
It's difficult to offer generic advice without knowing your acceptable downtime, the weekly data volume, your DB throughput, if you transact on the "old" partitions or just append to the new one and read the old ones, and how long you keep the historical data online.

However, to encourage discussion I'll suggest you consider simply creating your NEW partitions using daily ranges. From that moment, your daily insertions will go to the newly created partitions, while your old data will still be partitioned by weekly ranges. Sooner or later you're going to drop the old ones, leaving your table with only daily partitions. This option needs no downtime or surgical maneuvers.

If, on the other hand, you also need to "migrate" your weekly organized data to daily organized data, then consider using ALTER TABLE EXCHANGE PARTITION, it's the key to minimizing downtime. For each weekly partition, you need to create a separate daily partitioned table with seven partitions as select * from your weekly partition, and then exchange the table with your seven days with the partition with the whole week. You will need some index rebuilding, and some (brief) locking will occur as well.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 166 total points
ID: 34889258
What is your current setup? Automatic or manual work.

Consider splitting partition involves, inserting row from one partition to another(which is fine), and deleting rows from current partition(slow and resource consuming method).
You can think of inserting rows in different tables and in end you can truncate your current partition, then create new partition out of tuncated partition, and then exchange the partition.
This will generate less redo.
 
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 83 total points
ID: 34889288
Do you have to create 'new' partitions?  Depending on how the original partitions were set up, can you use sub partitions?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 14

Expert Comment

by:ajexpert
ID: 34889295
0
 

Author Comment

by:el123
ID: 34889366
we are using weekly partition, as mentioned earlier too, but most of the time, reports run only on daily basis, very rare we go to old partitions, means almost 80% of our reports execute against ... today, that's the reason of taking this approach of daily partitions.

I'm kind of in favor of Akenathon ...
create daily regular tables on weekly basis and exchange them with partition and then exchange them to newly created daily partitioned tables.
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 251 total points
ID: 34889444
Your report is not going to gain anything with your messing with the old data, it isn't even going to look at it.

So if you are mostly messing with today's data, use my first option: create from now onwards partitions by daily range, and leave the old ones alone. Nobody forces you to have uniform ranges in your partitions, so create the new ones every day and leave the other ones alone. ZERO downtime, ZERO surgery, ZERO risk :-)
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 166 total points
ID: 34889568
Consider the following

1. Feb 13 there are 10millions rows in current partition.
2. Feb 14 you need new partition at 5:00 am as per daily plan
3. 10 millions rows need to be copied in previous partition as per 5:00 am requirement.
4. Splitting partition will take lots of work.
5. you may think of exchange partition/truncate/create new partition.
6. Once implemented step 5, todays partition will be empty as of 5:00 am

when 80% will be loaded, because newly partition is empty as of 7:00 am.
Is there any job which changes the criteria on which partition is based.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34891147
Just a heads up: instead of relying that everything from "today" is contained within a single daily partition which you can scan fully, remember you could also leave the weekly strategy alone and use an index on the date to access "today's" rows.

Since you are "appending" both to the table and the index, the clustering factor will be very convenient, meaning you will not perform too many logical I/Os to access your data from the index. On the other hand, the full partition scan will use mutiblock I/O, so you have to try and see which side of the tradeoff is more convenient to your scenario
0
 

Author Comment

by:el123
ID: 34926319
but application is insisting, they only access one day of data, so only that day data/partition is important to them ... what and how to convince them?
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 251 total points
ID: 34933243
Well, you need to do standard application tuning on your report to reach the desired performance. That might result in changing the SQL, adding/removing/changing an index, and maybe switching to daily partitions. But remember your goal is to make the report run fast enough, not changing the partitioning strategy in the hope that it helps without having enough evidence to support that move.

At the very least make a proof of concept: clone your production DB and time your report with the weekly partitions. Drop the partition for the last week, create 7 empty daily partitions and import their data from your production DB. If it makes a huge difference it can be worth the move, but even in that case, it might not be the BEST possible move. Only application tuning can tell you the best possible move.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36116736
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question