Re: Speeding up import

That's incorrect as I also stated constraints=n, which along with indexes=n does away with ALL index creations in the initial import.

If you can come up with a method of importing databases faster, post it.

-Kevin
"Jim Gregory" <Jim.Gregory_at_ncr.com> wrote in message news:3ab6452e$1_at_rpc1284.daytonoh.ncr.com...
> I think that indexes will be created for any PK or Unique indexes even if
> the indexes=no clause is used for the export. The only time is saved on
> non-PK and non-unique indexes.
>
> --
> Jim Gregory
> Principal Consultant for Keane, Inc.
> Currently assigned to NCR, Dayton, OH
>
> Opinions are my own and do not reflect those
> of my employer or clients
> "Brian Dick" <bdick_at_home.com> wrote in message
> news:XOzs6.16333$PR.127535_at_news1.wwck1.ri.home.com...
> > 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 Mon Mar 19 2001 - 16:25:12 CST