Re: Speeding up import

I don't mean to start an import performance war here guys. Although, the newbies lurking here are surely ready to benefit from the spoils of such a war <g>.

So, what about my question concerning implicit versus explicit PK indexes? How do I get the latter?

"Kevin Brand" <kevin.brandx_at_tel.gte.com> wrote in message news:9960sl$lnh$1_at_news.gte.com...
>
> 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 - 20:36:34 CST