RE: Huge import takes a long time

The bummer is, while they call it a month, they set the same date to all transactions. But there may be some other way to do this.

I exported the partition from production and then tried to import into test, which had a matching partition/table.

The index is partitioned, and it seems that ONLY that index partition is getting hit as it is in a separate tablespace.

Yet, curiously enough, the tax on the syatem is pretty much nothing.

I also thought if they could have found some "division", they may have been able to use Pl/Sql routines and do "batch inserts" from one box to the other, but then again, may still have had the index problem. Not sure.

Michael Kline
Database Administration
Outside 804.261.9446
Cell 804.744.1545
3-9446  

> -----Original Message-----
> From: Dennis Williams [mailto:oracledba.williams_at_gmail.com]
> Sent: Thursday, July 21, 2005 8:37 PM
> To: Kline.Michael
> Cc: oracle-l_at_freelists.org
> Subject: Re: Huge import takes a long time
>
> Michael,
>
> Partition by day, a month is too large. You can direct path import
> into a small partition, build the local index on that partition, then
> exchange partition that partition into your real partitioned table.
>
> Dennis Williams
>
> On 7/21/05, Kline.Michael <Michael.Kline_at_suntrust.com> wrote:
> >
> >
> >
> >
> >
> > I've got a massive import that takes a long time. This is one I'd
love to
> > drop and recreate the index, but that alone would probably be almost
a
> > terabyte, so that is out of the question.
> >
> >
> >
> > This is a table partitioned by month and a normal partition is about
> > 20-24GB. As it's coming in, it's getting a lot of I/O wait on the
index
> > datafiles, but only the datafiles of that partition. The index is
also
> > partitioned.
> >
> >
> >
> > This is now 9.2.0.6, so just how high can you set up the buffer?
> >
> >
> >
> > Are there any other "tricks"?
> >
> >
> >
> > As I get to the "end" of the partition my import rate is getting to
be 2-3GB
> > per day.
> >
> >
> >
> > Yuck!!!
> >
> >
> >
> >
> >
> > Michael Kline
> > Database Administration
> > SunTrust Technology Center
> > 1030 Wilmer Avenue
> > Richmond, Virginia 23227
> > Outside 804.261.9446
> > STNet 643.9446
> >
> > Cell 804.744.1545
> > michael.kline_at_suntrust.com
> >
> >
> >
> >
> >
> > LEGAL DISCLAIMER
> > The information transmitted is intended solely for the individual or
entity
> > to which it is addressed and may contain confidential and/or
privileged
> > material. Any review, retransmission, dissemination or other use of
or
> > taking action in reliance upon this information by persons or
entities other
> > than the intended recipient is prohibited. If you have received this
email
> > in error please contact the sender and delete the material from any
> > computer.
> >
> > Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
> > [ST:XCL]
> >
> >

--
http://www.freelists.org/webpage/oracle-l