Re: Speeding up import

Take three separate exports:

1. just table data rows=y indexes=n constraints=n grants=n statistics=none
2. everything but the table data rows=n indexes=y constraints=y grants=y statistics=estimate ( or none see below )
3. really not an export at all do a quick exp/imp with no rows to get the Index DDL by using SHOW=Y and INDEXFILE=indexes.sql

Now, edit the indexes.sql file as follows:

1. include the UNRECOVERABLE clause for each statement
2. include a parallel clause for the largest ones ( if applicable )
3. create DDL for any implicit PK indexes ( see below )

Implicit PK indexes:
Note that if your PK indexes were not built with a create index command, they will most likely not show up in the indexes.sql file ( V7 ). So, if some of these are large, you'll want to write the DDL for them with the suggetions above and place this in the indexes.sql file.

Statistics:
If you don't want to wait for statistics during the final import, set statistics=none above in step 2). I'd recommend setting it to estimate, however you can shave off some time and come back later with more specific analyze statements.

To Import all this:

Import the file created in step 1) above using appropriate parameters ( ie. commit=?, recordsize=?, buffer=? ). This will get all the tables loaded with no statistics.

Now execute the indexes.sql file to generate all the indexes with unrecoverable and parallel clauses ( this is as fast as you can build indexes ).

Now import the file created in step 2) above with IGNORE=Y. This will get all the constraints and any indexes missed in the indexes.sql file. Note that if you do not build the DDL for implicit PK indexes, this step will create the index very slowly with the "alter table <table> add constraint" statement... Not what you want.

Hope this helps...

-Kevin

    "Brian Dick" <bdick_at_home.com> wrote in message news:1sgs6.16251$PR.125223_at_news1.wwck1.ri.home.com...
> An import of 12 million rows into about a dozen tables in our 8.1.6
 database
> takes about 6 hours. The export only took 12 minutes. What can we do to
> speed up the import?
>
> Later,
> BEDick
>
>
>
Received on Fri Mar 16 2001 - 10:48:28 CST