Convert table into partitioned table (Fast)

[mc...@hotmail.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.

[news.verizon.net]

<mc...@hotmail.com> wrote in message
news:0aacbe8b-6f9e-4ca6...@i29g2000prf.googlegroups.com...

Have you looked at creating a partitioned table and then doing an exchange?
It should take very little time.
Jim

[mc...@hotmail.com]

> 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..?

[DA Morgan]

INSERT INTO ... SELECT * FROM ....;
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[mc...@hotmail.com]

> > How would you achieve that..?
>
> INSERT INTO ... SELECT * FROM ....;
> --
> Daniel A. Morgan

My question was: is there a faster way to convert to a partitioned
table than "insert into .... select * from ....". I don't think you
read my post.

[DA Morgan]

I read your post ... my response was the answer.

If you want it fast use the APPEND hint.

[Steve Howard]

Hi,

If you go "INSERT INTO..." check your wait events on the load. If you
are doing it NOLOGGING, then you may have to set event 10359. We ran
into this a few months ago. The writes to the controlfile to indicate
it was an unrecoverable operation were taking about 30-40% of our
total elapsed time. Once we set it, we gained all that time back.

According to metalink note 1058851.6, there is no real impact to
setting it.

HTH,

Steve

[joel garry]

If the append hint and Steve's suggestion don't do it for you, perhaps
you can do the poor man's parallelization: do a bunch of insert to
select froms into tables with appropriate where clauses, and exchange
each of those for partitions. Not that I've ever tried it.

jg
--
@home.com is bogus.
http://asert.arbornetworks.com/2008/02/internet-routing-insecuritypakistan-nukes-youtube/

[mc...@hotmail.com]

> The writes to the controlfile to indicate
> it was an unrecoverable operation were taking about 30-40% of our
> total elapsed time.  Once we set it, we gained all that time back.

What event name was it waiting on....?

[mc...@hotmail.com]

> do a bunch of insert to
> select froms into tables with appropriate where clauses, and exchange
> each of those for partitions.  Not that I've ever tried it.
>

That sounds like a great idea..... That way I can do all of the
loading into the tables up front, and then do the partition exchange
during the outage window.

Thanks very much

[Shakespeare]

<mc...@hotmail.com> schreef in bericht
news:d515e7d1-9d16-48ea...@e23g2000prf.googlegroups.com...

>Thanks very much

You may be missing some records if they were inserted into the table between
the up front loading and the partition exchange.... but if this is not the
case, or not relevant, it may be worth while to try...

Shakespeare

[Steve Howard]

Hi,

IIRC, it was control file sequential read and control file parallel
write, but I would check for any event like 'control file%'

HTH,

Steve

[joel garry]

On Mar 7, 2:53 am, "Shakespeare" <what...@xs4all.nl> wrote:
> <mc...@hotmail.com> schreef in berichtnews:d515e7d1-9d16-48ea...@e23g2000prf.googlegroups.com...

True - but he did say date range partitioning strategy. Of course, he
could use a different strategy like insert into ... select from...
where... for all but the last partition, if he just has to put the
last partition during the window... as always, the best performance
comes from not doing something while checking the performance, even if
outside that window it's molasses. Assuming sufficient space.

jg
--
@home.com is bogus.

http://catless.ncl.ac.uk/Risks/25.07.html#subj13 Duhhhhhhh...