| Partitioning Tables [message #650346] |
Sat, 23 April 2016 01:13  |
 |
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
Hi,
Our application uses Oracle 11g, and all the 20 tables (i.e,. 20 different tables for different application module purpose) used by the application is Non-partitioned one. Each of these 20 have transactions spanning 6 months data.
Due to data volume growth we decided to convert all this partitioned tables to Non-partitioned based on transaction date.
What would be best approach and how much approximate time it will take to move data volume of 3 billion from all these 20 non-partitioned tables to partitioned tables.
Our approach is to
1.Rename all 20 tables.
2.Create non-partitioned tables.
3.insert the data to partitioned tables from the renamed tables.
4.Create indexes on the partitioned tables.
Is there any other best approach.
We thought about Exchange partitioning from non-partitioned tables.
But for that first we need to create multiple tables from non-partitioned table based on date and then do exchange partitioning.
i.e., say Table A have 6 months transaction data and if we want to convert this to a non-partitioned table with 6 partitions,
then we need to first create 6 tables based on transaction date from Table A, and then these 6 tables should be used to exchange partitions to
move to 6 partitions of the Partitioned table.
Is there any other way/approach that we can do exchange partitioning from a single source table to multiple partitions of a Partitioned table
based on the transaction date month.
Thanks,
Ninan.
[Updated on: Sat, 23 April 2016 01:16] Report message to a moderator |
|
|
|
| Re: Partitioning Tables [message #650347 is a reply to message #650346] |
Sat, 23 April 2016 01:21  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is 100% wrong:Quote:Due to data volume growth we decided to convert all this partitioned tables to Non-partitioned based on transaction date. You should not implement partitioning because of data volume. You should (perhaps) implement partitioning because it will solve a business problem. Examples might be "the overnight batch jobs don't finish until lunchtime" or "this screen takes 30 seconds to fresh and we need it in 3". You need to define a partitioning strategy (for indexes as well as tables) that will fix your business problem, and not introduce others. You need to prove this mathematically, and cost justify the price.
Get partitioning right, and the benefit may be huge. Get it wrong, and the results will be disastrous.
|
|
|
|
|
|
| Re: Partitioning Tables [message #650350 is a reply to message #650348] |
Sat, 23 April 2016 02:03 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ninan wrote on Sat, 23 April 2016 07:22Exactly. The batch jobs take over 10-15 hours to complete. This is the real intention to introduce partitioning.
Any thoughts on the approach? You need to begin by looking at the code in those batch jobs. You do not need to choose an expensive facility whose use might cause worse problems before identifying what the problem is.
|
|
|
|
|
|
|
|
|
|
| Re: Partitioning Tables [message #650402 is a reply to message #650401] |
Sun, 24 April 2016 05:56 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This may be a disaster, because you have not thought about the problem and devised a suitable strategy.
For example, you say that you intend to partition by date. What about the tables that do not have the date column? Do you intend to use reference partitioning? That will give you partitionwise joins, but do you really want to partition your dependent tables like that? Are you sure you wouldn't prefer to hash partition them on a different key? What about indexes? Global or local? If it is batch DML and DDL jobs, you probably want local indexes. OK, but that may cripple your queries.
However, you have ignored everything I've said so far, so I won't waste your time any more.
|
|
|
|
| Re: Partitioning Tables [message #650413 is a reply to message #650402] |
Sun, 24 April 2016 12:21 |
|
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
This is how see it.
Oracle never have any solution to user's problems once you purchase the Software and the hundreds of options comes with it.
But it always promises to solve your problems before you buy it.
Like this they offer :
------------------
Table partitioning delivers advantages in three main areas
performance,
maintenance, and
availability.
Performance advantages
A table is partitioned based on a criterion such as the value for a particular column. If a query requests data with a particular selection condition that would eliminate a complete partition, Oracle automatically ignores that partition in executing the query. In this way, you can partition a large table to get the advantages of a smaller table.
For instance, you may have a very large table with all of the orders for a year. Most queries, though, only request data for a single month. You could partition the table by month, so that these common queries would, in effect, be accessing a smaller table, while the complete set of data still would be available.
The Oracle cost-based optimizer also takes this into consideration when deciding how to execute an SQL statement.
Maintenance advantages
Most maintenance operations can be performed on a single partition. You can backup or recover a partition rather than the entire table. In this way, you can significantly reduce the time required to perform maintenance operations.
In the above example of a table partitioned by month, you could perform daily backups on the current month's partition, and less frequent backups on previous months. In an extremely large database, this can make the difference between having enough time to complete a backup in off-business hours or not.
--------------
Oh Great!! The user goes and buys the software and the licenses .....Oracle is happy they have made lot of money.
Now when the user is going to use it, haaa don't use it you may face this problem or that problem or this problem ...100s of them...
Why the heck did Oracle did'nt say when they were selling....oh then Oracle is just another Software giant selling products after products..with 100s of licensing options....blah blah blah...
They never have any solutions to any problems...they have problems, more problems..if you use A option this problem..if you use B option that problem..
Why the heck do they make so many options with so many problems and create more problems rather than giving simple solutions to user's problems.
So I am enlightened now to not use Oracle..Thanks Oracle!!!!
A frustrated Oracle User!!!!
[Updated on: Sun, 24 April 2016 12:33] Report message to a moderator |
|
|
|
| Re: Partitioning Tables [message #650417 is a reply to message #650413] |
Sun, 24 April 2016 14:40 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ninan wrote on Sun, 24 April 2016 13:21This is how see it.
Oracle never have any solution to user's problems once you purchase the Software and the hundreds of options comes with it.
But it always promises to solve your problems before you buy it.
Like this they offer :
------------------
Table partitioning delivers advantages in three main areas
performance,
maintenance, and
availability.
A frustrated Oracle User!!!!
Did Oracle push you into buying partitioning option stating it will solve YOUR problem? Ferrari offers fast cars. Would you buy Ferrari because Ferrari claims it is fast and then blame Ferrari for not solving your problem of delivering load on time when roads are jammed? Your job runs too long, right? Did you identify bottlenecks before rushing into buying Ferrari. Scooter could be much better solution on jammed roads. Maybe you need to split load between many scooters each moving part of the load - in other word maybe you can run job in parallel. Delivering load at different time could be another solution - maybe you have scheduling issue and too many concurrent and/or conflicting jobs are running at the same time? So far all we hear is bitching and moaning and blaming Oracle. You didn't provide a shred of evidence pointing to your issue - no explain plans, no AWR reports. Are you a DBA? If so act like one. Examine what is going on and why. Only then offer solution and arguments why partitioning will or will not solve the issue. If you are not a DBA then involve your DBA. Oracle database isn't out-of-the-box product for all type of databases. Heavy load and/or high volume databases need tuning. And in some cases even tuning will not help if database was designed incorrectly without proper scalability in mind.
SY.
[Updated on: Sun, 24 April 2016 18:55] Report message to a moderator |
|
|
|
|
|
| Re: Partitioning Tables [message #650571 is a reply to message #650488] |
Wed, 27 April 2016 13:56 |
 |
jyeager
Messages: 4
Registered: April 2016
Location: Nashville
|
Junior Member |
|
|
dbms_redefinition will do the job if there is a requirement that the tables remain available to active database sessions the whole while that the partitioning is taking place.
It is much slower, however.
A few years back I partitioned a lot of our ORDER tables based on calendar year. I think it took a weekend to do most of the tables individually and it was a multiple-weekend project to get all of the target tables partitioned.
If at all possible, it would be much better (faster) to create the new partitioned table, copy all data, then drop and rename.
Partitioning based on date is useful only when you have jobs that need to process rows within a date range. If you index the date column in question, it will be fine, except that if you are looking back over 15, 30, 180 days worth of data the optimizer won't likely use the index.
That's the reason we implemented on these tables. We have open orders (backorder) situations that we need to pick against far in to the past.
It has worked well.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
