Re: Speeding up import

Thanks for the detail steps. I have a couple of questions, though, about the implicit PK indexes.

Are you saying that I should not be using the "PRIMARY KEY(...)" clause when I create my tables, but instead explicitly create unique indexes on the PK columns? Unfortunately, I have third party software that uses the PK constraints in various ways. Is there a way to get explict PK indexes and PK constraints, but not screw up the exp/imp?

Also, I'm not sure how to do step 3). Sorry about being so dense, but it has been a long week.

"Kevin Brand" <kevin.brandx_at_tel.gte.com> wrote in message news:98tg13$gd1$1_at_news.gte.com...
>
> 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:
> a) include the UNRECOVERABLE clause for each statement
> b) include a parallel clause for the largest ones ( if applicable )
> c) 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 - 20:17:27 CST