Home » RDBMS Server » Performance Tuning » split a large table to small pieces? (Oracle 10G, Solaris 10)
icon5.gif  split a large table to small pieces? [message #501279] Mon, 28 March 2011 05:55 Go to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
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 #501281 is a reply to message #501279] Mon, 28 March 2011 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
(partition table by month)?

Seems one track to think about.

Quote:
What is the best approach?

Define "best". Against which criteria?

Regards
Michel
Re: split a large table to small pieces? [message #501282 is a reply to message #501281] Mon, 28 March 2011 06:21 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
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 #501640 is a reply to message #501282] Wed, 30 March 2011 06:55 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
Member
Please help someone tell me how to resolve this issue.
Re: split a large table to small pieces? [message #501643 is a reply to message #501640] Wed, 30 March 2011 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You said it: partition by month.

Regards
Michel
Re: split a large table to small pieces? [message #501647 is a reply to message #501643] Wed, 30 March 2011 07:20 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
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 #501650 is a reply to message #501647] Wed, 30 March 2011 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it chunk per chunk.

Regards
Michel
Re: split a large table to small pieces? [message #501687 is a reply to message #501650] Wed, 30 March 2011 12:42 Go to previous messageGo to next message
John Watson
Messages: 4370
Registered: January 2010
Location: Global Village
Senior Member
Have you considered using dbms_refinition ?
Re: split a large table to small pieces? [message #501688 is a reply to message #501687] Wed, 30 March 2011 12:57 Go to previous messageGo to next message
Kevin Meade
Messages: 1924
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 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
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 #501748 is a reply to message #501687] Thu, 31 March 2011 01:42 Go to previous messageGo to next message
Tlg13team
Messages: 82
Registered: June 2008
Location: MGL
Member
Hi John Watson,

Quote:
dbms_refinition


Did you tell me about dbms_redefinition?
Re: split a large table to small pieces? [message #501750 is a reply to message #501748] Thu, 31 March 2011 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you think it is?
Did you find a dbms_refinition package?
Is "refinition" an english word?

Regards
Michel
Re: split a large table to small pieces? [message #501760 is a reply to message #501750] Thu, 31 March 2011 03:40 Go to previous message
Kevin Meade
Messages: 1924
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
Previous Topic: Can I use Oracle Hint to use index in a query which uses UNION
Next Topic: Performance tuning of query with self join
Goto Forum:
  


Current Time: Wed Jul 23 11:50:50 CDT 2014

Total time taken to generate the page: 0.05960 seconds