FreeLists / oracle-l / RE: URGENT: IMPORT HELP

["Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>]

Paula,
 
Why are you concerned about creating the PKs correctly?  Are you importing from 
a static file?  Did you have consistent=y when the export was run?  Are you 
importing from a live database via named pipe?
 
In terms of improving performance, I would:
 
1.)  Disable all triggers.
2.)  Disable all FKs, then all PKs and UKs.
3.)  Mark all indexes unusable.
4.)  Run the import with indexes=n constraints=n
5.)  alter index rebuild for all unusable indexes, with nologging and 
optionally parallel.
6.)  Create any indexes which were dropped when PK or UK was disabled, 
preferably this time as non-unique (so they won't be dropped w/ future 
refreshes) and with nologging and optionally parallel.
7.)  If any indexes were created or built w/ parallel, careful to set the 
parallel degree back to 1, or you may see unexpected execution plans.
7.)  Enable all PKs and UKs.  (Should go fast, if indexes already exist.)
8 .)  Enable FKs.  (If you're brave, enable novalidate to make it really move.)
9.)  Enable all triggers.
 
 
Hope that helps,
 
-Mark

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

Ours is the age that is proud of machines that can think and suspicious of men 
who try to.  --H. Mumford Jones, 1892-1980

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Paula Stankus
Sent: Tuesday, August 01, 2006 5:19 PM
To: oracle-l
Subject: Re:URGENT: IMPORT HELP


Using Oracle 8.1.7, Solaris 2.9
 
Trying to load a large amount of data using import.
 
The  schema needs to be the same and only the data reloaded.  There are 
triggers, constraints (pk, fk, etc.) and pk indexes.
 
I have been loading with pk's enabled and pk indexes (not fk and fk indexes) 
and the process is very slow.  
 
I have been running the import with statistics=n, indexes=n
 
I am tempted to disable all constraints and drop pk indexes but I am afraid 
that I might have trouble creating the pk's properly.  Can I used the indexfile 
option to create the pk's properly and will this make a significant difference 
in the import.
 
What about after insert triggers?  What is the best way to handle the after 
insert triggers?
 
Any help would be appreciated.  I have been spending a lot of time with this 
data load.
 
Thanks,
Paula

________________________________

Groups are talking. We´re listening. Check out the handy changes to Yahoo! 
Groups. 
<http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=41144/*http://groups.yahoo.com/local/newemail.html>