Re: Speeding up import

Kevin Brand wrote:
>
> 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >

similarly running 'strings' on a dump file (with no data) is an effective way of obtaining the constraint commands (grep-ing for a leading 'alter')

hth
connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"