Home » RDBMS Server » Performance Tuning » efficiently populating tables
efficiently populating tables [message #231385] Mon, 16 April 2007 20:43 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
hey guys/girls,

if i have to load millions of records; generally speaking, what method is more efficient, i have to reload data once a week:

1) a) drop table
b) create table temp_table as (SELECT * FROM query_tables/views)

or

2) a) drop table
b) create table
b) do bulk collect into table of records and then do forall insert into the new table.

or

3) a) delete table
b) do bulk collect into table of records and then do forall insert into the new table.

what do you guys think?

thanks,
John.
Re: efficiently populating tables [message #231387 is a reply to message #231385] Mon, 16 April 2007 21:12 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Hi John

It depends what data type you have for that table.
I am having one more option,
1> Truncate table... faster than Delete and structure remains same and DDL so auto commit.
2> Import the data.

or CTAS is also fine, after dropping,Which is your first option.!!!



Regards,
Harsh
Re: efficiently populating tables [message #231395 is a reply to message #231387] Mon, 16 April 2007 22:45 Go to previous messageGo to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi

Use SQLLoader with truncate option, Truncate the table and Loading the data will happen in single command.


Regards
Soma.
Re: efficiently populating tables [message #231396 is a reply to message #231385] Mon, 16 April 2007 22:51 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>what do you guys think?
You have the data & you have the need.
Benchmark EACH case & post the results back here so everyone can benefit.
How will you KNOW for sure which is the fastest choice unless & until you run your own benchmarks?
Listening to responses here is as effective as throw darts in the dark.
The choice is yours.
Previous Topic: Archive log
Next Topic: SQL Performance
Goto Forum:
  


Current Time: Sun Dec 04 00:34:06 CST 2016

Total time taken to generate the page: 0.10005 seconds