Home » RDBMS Server » Performance Tuning » order of drop,create and create index during data load
order of drop,create and create index during data load [message #283615] Tue, 27 November 2007 13:24 Go to next message
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 #283617 is a reply to message #283615] Tue, 27 November 2007 13:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Load data without indexes.
Indexes may help with select, but will hinder insert.

>>load from a data file
Also depends how you load the file.
sqlldr direct load might help.

And other question I had, is already been asked by Ana

[Updated on: Tue, 27 November 2007 13:30]

Report message to a moderator

Re: order of drop,create and create index during data load [message #283618 is a reply to message #283615] Tue, 27 November 2007 13:28 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
WHY are you dropping tables?
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 Go to previous messageGo to next message
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 #283620 is a reply to message #283619] Tue, 27 November 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
truncate the tables instead of droppping.
More truncate them with keep storage option.

The best order is the one use by import.
So export some tables and import them into a test database, see the order used by import in the generated log.
Import creates the unique and primary key at the beginning, if you trust your data or have a process to eliminate the data that violate the above constraints then create these ones at the end.

Regards
Michel
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Rebuild of indexes and 'Sweeper' referred in Ask tom site Options
Next Topic: Problem with SQL
Goto Forum:
  


Current Time: Tue Dec 06 14:15:35 CST 2016

Total time taken to generate the page: 0.06301 seconds