Home » RDBMS Server » Server Utilities » sqlloader vs external tables (9i/10g/11g)
sqlloader vs external tables [message #493658] Wed, 09 February 2011 05:00 Go to next message
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 #493660 is a reply to message #493658] Wed, 09 February 2011 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since they use the same technology under the hood I doubt there is going to be much difference.
Why don't you test it yourself and find out?
Re: sqlloader vs external tables [message #493661 is a reply to message #493660] Wed, 09 February 2011 05:06 Go to previous messageGo to next message
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
Member
I do not have so much of data.
it is a interview question
Re: sqlloader vs external tables [message #493666 is a reply to message #493661] Wed, 09 February 2011 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then go with my first answer
Re: sqlloader vs external tables [message #494163 is a reply to message #493666] Fri, 11 February 2011 11:02 Go to previous messageGo to next message
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 #494166 is a reply to message #494163] Fri, 11 February 2011 11:11 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions!
ROW by ROW is SLOW by SLOW.
Bulk processing is faster.
Re: sqlloader vs external tables [message #494171 is a reply to message #494166] Fri, 11 February 2011 11:23 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course the data doesn't get loaded during external table CREATION. It only gets loaded once you fire a SELECT on the table.
Re: sqlloader vs external tables [message #494175 is a reply to message #494171] Fri, 11 February 2011 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 13895
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 67569
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
Previous Topic: Import dump
Next Topic: Creating a SQL Loader ctl file with over 1000 columns
Goto Forum:
  


Current Time: Thu Dec 03 23:33:59 CST 2020