| sqlloader vs external tables [message #493658] |
Wed, 09 February 2011 05:00  |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
I would like to know which of the above is faster for the same conditions.
i.e. If I am loading 1 million rows for the same conditions which will perform faster?
Is any other info is needed please let me know!
|
|
|
|
|
|
|
|
|
|
| Re: sqlloader vs external tables [message #494163 is a reply to message #493666] |
Fri, 11 February 2011 11:02  |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
Hi all,
I created million records correspoding to empno and I took this field as number(10) field length.
I loaded this data using sqlloader and I found it was taking almost 20 seconds
Now I created an external table and it got created only in .6 seconds.
Hence external table creation was much faster for the same data file which had million records corr to empno.
Any suggestions!
|
|
|
|
|
|
|
|
| Re: sqlloader vs external tables [message #494175 is a reply to message #494171] |
Fri, 11 February 2011 11:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Just to expand ThomasG's point: An external table is just a pointer to the file. None of the data in it exists in the DB. You have to select the data from the external table and insert it into a real table.
The time taken to do that is what you need to compare to sqlloader.
|
|
|
|
| Re: sqlloader vs external tables [message #494263 is a reply to message #494175] |
Sat, 12 February 2011 09:36 |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
I created one more table and ran the insert command
SQL> set timing on
SQL> insert into newemp2 select * from newemp;
1000029 rows created.
Elapsed: 00:00:01.52
It just took 1.52 secs from external table to load data into one more table in databse
as comapared to 20 secs while loading the data from sqlloader.
Hence I am finding external table processing is much faster
|
|
|
|
| Re: sqlloader vs external tables [message #494265 is a reply to message #494263] |
Sat, 12 February 2011 09:52 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I think you will get the same performances if you specify appropriate rows and bindsize parameters which is automatically set high with external table if you are alone on your server.
After all, they use the same engine.
Anyway, I prefer external table (maybe just because I'm not an expert in SqL*Loader parameters and control file) but you then must have the file on the database server which may not be possible in all cases.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
