Home » RDBMS Server » Performance Tuning » Export from table vs external table (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Export from table vs external table [message #645461] Mon, 07 December 2015 04:53 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member
Hi,

I have an external table consists of 8(+) millions records.
I need to export the table in tab delimited file. I will export using SQL Developer.

Would I get any performance benefit if I export from the regular table in place of external table?

Regards
Mokarem

Report message to a moderator

Re: Export from table vs external table [message #645462 is a reply to message #645461] Mon, 07 December 2015 04:58 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
As you'll have to read the external table into a "regular" table before you can even begin the export, I would think it would be slower.

However, unless you are doing very complex transformations and selections, why go through the database at all? Is it not possible to do it with awk or sed ?

Report message to a moderator

Re: Export from table vs external table [message #645465 is a reply to message #645462] Mon, 07 December 2015 05:08 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member
Thanks

I can export from the external table directly using the SQL Developer.

Just out of curiosity want to know which is better considering the performance.

Regards
Mokarem

Report message to a moderator

Re: Export from table vs external table [message #645469 is a reply to message #645465] Mon, 07 December 2015 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again, if you considering performance and the data isn't in the DB to start with, then any solution using the DB isn't that performant.

Report message to a moderator

Re: Export from table vs external table [message #645531 is a reply to message #645461] Tue, 08 December 2015 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

If it is an external table then it is more likely it already is a CSV file.

Report message to a moderator

Re: Export from table vs external table [message #645533 is a reply to message #645531] Tue, 08 December 2015 03:40 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member


Yes, It is already a csv file.
I need to extract some records from it conditionally.

Report message to a moderator

Re: Export from table vs external table [message #645536 is a reply to message #645533] Tue, 08 December 2015 03:46 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
mokarem wrote on Tue, 08 December 2015 09:40


Yes, It is already a csv file.
I need to extract some records from it conditionally.
So use grep as well as awk.


By the way, I wish you would not say "record" when you mean "row" Smile

Report message to a moderator

Re: Export from table vs external table [message #645538 is a reply to message #645536] Tue, 08 December 2015 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

As it is a file, you may say record or line. Smile

Report message to a moderator

Re: Export from table vs external table [message #645540 is a reply to message #645536] Tue, 08 December 2015 04:21 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member
From client I can access the file using oracle external table only.
In such case how can I use the grep/awk?

Report message to a moderator

Re: Export from table vs external table [message #645542 is a reply to message #645540] Tue, 08 December 2015 04:52 Go to previous message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Ask your sysadmin to send it to you.
This is an organizational issue NOT and Oracle one.
Your input is a file, your output is file a file part of the input, there is no reason to involve an Oracle database there.

Report message to a moderator

Previous Topic: MULTI TABLE INSERT USING BULK COLLECT
Next Topic: PLAN_TABLE_OUTPUT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 13:43:45 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.