| efficiently populating tables [message #231385] |
Mon, 16 April 2007 20:43  |
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  |
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 #231396 is a reply to message #231385] |
Mon, 16 April 2007 22:51 |
 |
BlackSwan
Messages: 26766
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
