Re: Convert table into partitioned table (Fast)
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 06 Mar 2008 03:11:48 -0800
Message-ID: <1204801905.76127@bubbleator.drizzle.com>
>> <mc..._at_hotmail.com> wrote in message
>>
>> news:0aacbe8b-6f9e-4ca6-ba9c-0d27c6c88bc1_at_i29g2000prf.googlegroups.com...
>>
>>
>>
>>
>>
>>> 10g Release 2
>>> Whats the fastest way to convert a very large table into a partitioned
>>> table. The operation doesn't have to be done online but it needs to
>>> completed as quick as possible.....
>>> Current plan is to:
>>> 1. Rename the existing unpartitioned table
>>> 2. Create new partitioned table
>>> 3. Insert into partitioned table select * from unpartitoned table
>>> 4. Create indexes, constraints, etc
>>> Is there a faster way to achieve this.
>>> I've tried using direct loading using the /*+ APPEND */ hint but this
>>> doesnt increase the throughput significantly. Delaying the index
>>> creation until after the load however does massively improve load
>>> times.
>>> Any other ideas..?
>>> Thanks in advance.
>> Have you looked at creating a partitioned table and then doing an exchange?
>> It should take very little time.
>> Jim- Hide quoted text -
>>
>> - Show quoted text -
Date: Thu, 06 Mar 2008 03:11:48 -0800
Message-ID: <1204801905.76127@bubbleator.drizzle.com>
mccmx_at_hotmail.com wrote:
> On Mar 5, 5:55 pm, "news.verizon.net" <kenned..._at_verizon.net> wrote:
>> <mc..._at_hotmail.com> wrote in message
>>
>> news:0aacbe8b-6f9e-4ca6-ba9c-0d27c6c88bc1_at_i29g2000prf.googlegroups.com...
>>
>>
>>
>>
>>
>>> 10g Release 2
>>> Whats the fastest way to convert a very large table into a partitioned
>>> table. The operation doesn't have to be done online but it needs to
>>> completed as quick as possible.....
>>> Current plan is to:
>>> 1. Rename the existing unpartitioned table
>>> 2. Create new partitioned table
>>> 3. Insert into partitioned table select * from unpartitoned table
>>> 4. Create indexes, constraints, etc
>>> Is there a faster way to achieve this.
>>> I've tried using direct loading using the /*+ APPEND */ hint but this
>>> doesnt increase the throughput significantly. Delaying the index
>>> creation until after the load however does massively improve load
>>> times.
>>> Any other ideas..?
>>> Thanks in advance.
>> Have you looked at creating a partitioned table and then doing an exchange?
>> It should take very little time.
>> Jim- Hide quoted text -
>>
>> - Show quoted text -
> > Yeah, I'm aware of the feature but you have to exchange the > unpartitioned table with 'one' of the partitions in the partitioned > table. But this doesnt actually split up the data based on my > partitioning strategy, i.e. date range > > How would you achieve that..?
INSERT INTO ... SELECT * FROM ....;
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Mar 06 2008 - 05:11:48 CST