Table Partition [message #468422] |
Fri, 30 July 2010 01:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
amit_kiran
Messages: 50 Registered: July 2006 Location: UK
|
Member |
|
|
Hi,
Currently I am having large size table in Live environment. This table holds more than 12 Million records and is having 11 Indexes.
Due to this Insert of approx 0.3 Million records takes 3 hrs.
I need to reduce time taken for Insert.
Can you please help if Partitioning will improve performance of Inser statement.
if not, any other suggestions will be helpful.
Thanks in advance.
Amit.
|
|
|
|
Re: Table Partition [message #468445 is a reply to message #468425] |
Fri, 30 July 2010 02:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8949 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You need to do a lot of research when implementing partitioning because there are many options, and if you get it right it may help, but if you get it wrong it may be disastrous. Investigate the effect on wait events and execution plans of various partitioning strategies before committing to it, because it is expensive in effort, time, and money - so you must prove that it works before investing.
To take your example, I would begin with the wait events. If the 3 hours is because of wait events to do with redo generation, then partitioning will have no effect at all. If it is to with buffer busy waits on the table (the indexes are a different matter), hash partitioning might help. But then you need to think about the effect on execution plans for subsequent queries.
|
|
|
|
Re: Table Partition [message #468590 is a reply to message #468463] |
Fri, 30 July 2010 22:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
How are you doing the INSERT? Is it a single INSERT? Many INSERTs? SQL*Loader?
Using a direct-path load could be a good strategy if you have no Foreign Key constraints or triggers. Check out http://www.orafaq.com/tuningguide/direct%20path.html
You should also look into PARALLEL DML.
These approaches will have MUCH more impact on INSERT performance than partitioning the table.
Ross Leishman
|
|
|
|