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
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: 7220
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 ?
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
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: 12992
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.
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: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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.
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: 7220
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
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: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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?
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: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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.

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


Current Time: Sat Jan 20 06:06:24 CST 2018

Total time taken to generate the page: 0.01379 seconds