OraFAQ Forum: Performance Tuning » Export from table vs external table

Home » RDBMS Server » Performance Tuning » Export from table vs external table (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Export from table vs external table [message #645461]

Mon, 07 December 2015 04:53

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

John Watson
Messages: 8978
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

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

cookiemonster
Messages: 13967
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

Michel Cadot
Messages: 68760
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

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

John Watson
Messages: 8978
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

Michel Cadot
Messages: 68760
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

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

Michel Cadot
Messages: 68760
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 18 10:13:35 CDT 2025