 split a large table to small pieces? [message #501279] |
Mon, 28 March 2011 05:55  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
I have several large tables in the live system! Those table are store historical information.
current situation:
Now, A table record was 129 million rows.
Every month added 4.5M records to this table.
This table data size 17GB and index size 28GB.
I have only 30 GB available free space on disk!
How to split this table to small pieces (partition table by month)?
What is the best approach?
I would like to do partitioning on this table month by month.
[Updated on: Mon, 28 March 2011 05:57] Report message to a moderator |
|
|
|
|
|
| Re: split a large table to small pieces? [message #501282 is a reply to message #501281] |
Mon, 28 March 2011 06:21  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi Michel,
Every night do not any transaction on this table (approximatively 5 hour).
I can do this split process to several step (Every night one year data to move partition table). but, a step should complete within 5 hour.
Also I would like to finish this task within a week.
|
|
|
|
|
|
|
|
| Re: split a large table to small pieces? [message #501647 is a reply to message #501643] |
Wed, 30 March 2011 07:20 |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
How I do?
A table is non-partitioned table.
i have create B table. this is partitioned table by month. i try to insert A table records to B table, but getting error about undo tablespace is full!
I don't extend undo tablespace, because I don't have enough free space on hard disk.
How to resolve this issue?
|
|
|
|
|
|
|
|
| Re: split a large table to small pieces? [message #501688 is a reply to message #501687] |
Wed, 30 March 2011 12:57 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I hate this post quick reply capability. I vote we remove it. It has never saved me any time.
Now for the OP:
You first should explain why a 147 million row table is a problem for you. Why do you think you need to do something?
If you fear running out of disk space you have several options, my favorite being get more disk space.
If you are trying to improve performance than you should explain what the performance issue is as you understand it.
If better management of the table is what you seek then explain what management complications you are having now.
Partitioning is an advanced feature. It was added to Oracle to address specific situations. You should read about partitioning to understand why it exists, then you can likely tell us why it will help you.
Kevin
[Updated on: Wed, 30 March 2011 13:00] Report message to a moderator |
|
|
|
| Re: split a large table to small pieces? [message #501746 is a reply to message #501688] |
Thu, 31 March 2011 01:34 |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi Keven,
This table is core table of my solution and many query run on database using this table with join case. This table story all data since 2004 year.
Now, select operations are run very slow.
I think that if I prepare partition on this table then use partition pruning on that table.
Also, I would like extract oldest data on this table.
I can't extend storage box, because I don't have budget.
|
|
|
|
|
|
|
|
| Re: split a large table to small pieces? [message #501760 is a reply to message #501750] |
Thu, 31 March 2011 03:40 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
queries can run slow for many reasons and have a different solution for each reason.
Do you have queries that would benefit from partition pruning?
You can always delete old rows to get back reusable space as long as your pct_free/pct_used parameters are set to allow it.
Does your table have any indexes? Try creating an index using the columns you were thinking of partitioning on.
Also, as was stated, do a google for dbms_redefinition.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
