Re: Import time taking so long <<<===============================================================

To speed up the import.

1. Export the source data without indexes and constraints.
2. Create script to recreate indexes, primary keys and foreign keys in destination database.
3. Drop indexes in destination database.
4. Disable primary key, foreign key contraints in destination database.
5. Disable the database triggers.
6. Import the data.
7. Enable the database triggers.
8. Recreate indexes using the earlier script.
9. Recreate primary key and foreign keys.

Hope this helps.
Ashish

In article <7asgso$olp$1_at_nnrp1.dejanews.com>,   mark.powell_at_eds.com wrote:
> In article <xmdA2.2991$_m5.689408_at_newsread1-mx.centuryinter.net>,
> alu_at_dairynet.com (A Ustby) wrote:
> > Another Oracle question/observation....
> >
> > We have an instance (v7.3.3) that has 1000+ tables in it (purchased
> > application). A couple of the tables have 1.5 million rows in them. One of
> > these tables has 14 indexes on it. On the same box we run both production
and
> > test instances. We have the Oracle parameters (memory, sort area, etc) set
> > lower for the test instance. When we load production instance, it takes 17
> > hours. When we load the same data into the test instance, it takes 41 hours.
> > Both loads were done when the box was quiet.
> >
> > We are using the Oracle import utility. It appears the excess time is in the
> > index build phase. Could this be affected (that much !) by the Oracle sort
parm
> > size ? Any other insight ?
> >
> > The thing takes so long, we haven't done too much experimenting.
> >
> > Thanks.......... Art
> >
>
> Yes, loading the indexes with the table is in my experience a lot slower than
> loading with indexes=n then re-building the indexes from index build scripts
> when large tables are involved. You can help the import by setting buffer=
> to a large number and making sure a large rollback segment is available.
> Since import does not let you specify use of a specific rollback segment I
> take all of them offline except the one I want the import to use. You can
> help the index builds by altering your temporary tablespace initial and next
> larger during the index rebuilds using the import or manual rebuilds. This
> will only work when other jobs can be kept of the system while the import is
> running.
>
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Feb 25 1999 - 14:43:17 CST