Re: Speeding up import

Sorry, my news server didn't catch your reply post below.

There was a recent discussion in this NG about implicit vs. explicit indexes that enforce PK indexes. For your purposes, I'd just generate the DDL by hand to build the PK indexes before the constraints are added during the import. You may even find an option in your third-party tool that breaks these statements up for you.

As for step 3, here's the details:

o Get an export as follows ( this should complete very quickly ):

    exp userid=<whatever> owner=<target_schema> rows=n indexes=y constraints=y grants=n statistics=none file=dummy.dmp

o using the above dummy.dmp file, import as follows

    imp userid=<whatever> SHOW=Y fromuser=<target_schema touser=<target_schema> indexes=y rows=n indexfile=indexes.sql full=n file=dummy.dmp

Note that the imp call above will not affect your schema ( no SQL will be executed against TOUSER ) as long as you use the SHOW=Y option ( this is very important ).

Now you will have a file called "indexes.sql" that contains the DDL for all tables and indexes. The table create statements are commented out and can be removed, unless you want to keep them. You'll want to edit this file as described and to change storage parameters as necessary. If your platform is UNIX, here's a quick script I use for the initial edit. It gets rid of all the Table DDL, adds UNRECOVERABLE to all index create statements and places blank lines between them:

echo "\nInitial script edit..."
cat indexes.sql|grep -v -e "^CONNECT " -e "^REM" | \

        sed -e "s/;/ unrecoverable ;>/g" -e "y/>/\n/"

Hope this helps

-Kevin

"Brian Dick" <bdick_at_home.com> wrote in message news:Smzt6.17241$PR.136586_at_news1.wwck1.ri.home.com...
> 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 Tue Mar 20 2001 - 09:49:21 CST