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 - 11:42:41 CST
