| order of drop,create and create index during data load [message #283615] |
Tue, 27 November 2007 13:24  |
beeky
Messages: 6
Registered: February 2007
|
Junior Member |
|
|
We use a script to set up a db with a subset of production data for testing specific features. The script seems to run quite slowly given what it does so I was wondering if the order of actions could affect the speed.
Currently the script does the following in the order listed.
1. drop all tables
2. create one table and load from a data file
3. repeat 2. for all tables
4. create constraints for all tables
5. drop all indexes
6. create appropriate indexes for all tables
I'm wondering if the following order would be faster.
1. drop all indexes
2. drop all tables
3. create one table, add constraints and indexes then load
4. repeat for all tables.
I guess my question is, given this scenario, what would be the order that produces the fasted load?
I plan on testing this myself but an average load takes more than an hour so this forum should provide a quicker answer!
Thanks,
--beeky
|
|
|
|
|
|
|
|
| Re: order of drop,create and create index during data load [message #283619 is a reply to message #283615] |
Tue, 27 November 2007 13:34  |
beeky
Messages: 6
Registered: February 2007
|
Junior Member |
|
|
mahesh and anacedent,
Thanks for the rapid response.
In answer to mahesh's question. All our archived data is in text form because it is used for a variety of other purposes. It is all we developers have to work with.
Why are we dropping tables? I must admit I never thought about this but the primary reason is that the data sets changed fairly frequently and older data is often not suitable for testing.
-=beeky
|
|
|
|
|
|
| Re: order of drop,create and create index during data load [message #284493 is a reply to message #283615] |
Thu, 29 November 2007 21:47 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
May I ask,
What indexes are you dropping in your first sql ordering? When you drop tables, all indexes on those tables drop as well so I am not sure what you are trying to drop. If this is truely your order, then you will be dropping nothing, and additionally you would be seeing errors when you tried to drop indexes that supported your unique and primary keys.
How are you loading data from file? SQLLoader is a good way, but something like UTL_FILE would be slow, as would individual insert statements.
If you are using sqlloader, then what parameters/settings are you using to do the load? There are settings that reduce redo/logging etc., you should be using these.
You might also with to consider using external tables for this process. You could then easily create a process that took advantage of parallel load and parallel index builds.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
