OraFAQ Forum: SQL & PL/SQL » How to insert with100 Million record's sql query result into another table 1 million by 1 million

Home » SQL & PL/SQL » SQL & PL/SQL » How to insert with100 Million record's sql query result into another table 1 million by 1 million (Oracle 10g)

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

How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631123]

Fri, 09 January 2015 10:54

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

1) How to insert (Huge SQL query results) with 100 Million record's sql query result into another table, 1 million by 1 million insert/update into target table

using Bulk operation and limit size for 1 million also doesnt have enough buffer memory. is there any way to process without effect the memory area?

SQL query results 100 Million --> bulk collect into temp_table limit size 1 million--> insert/update into target_table.

Thanks and Regards,

Victory

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631125 is a reply to message #631123]

Fri, 09 January 2015 10:58

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just use INSERT SELECT maybe with APPEND and/or PARALLEL hint if your hardware and concurrent workload allow them.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631127 is a reply to message #631123]

Fri, 09 January 2015 11:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Sorry, but your question is not clear. What are these memory area, temp_table, buffer memory? In what context are you saying? Please elaborate what exactly you are trying to achieve.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631129 is a reply to message #631127]

Fri, 09 January 2015 11:07

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

Thanks for your response Lalit.

My question is

1. Eventhough, when you try to fetch the 100 Million records in single fetch through bulk collect to process these rows if you doesnt have enough buffer memory in oracle (particularly in your session)to hold theses records, how you will process these many records? without getting any issues or error in buffer memory overflow.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631130 is a reply to message #631129]

Fri, 09 January 2015 11:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>without getting any issues or error in buffer memory overflow.

post reproducible test case where you actually get "buffer memory overflow".
Are you trying to solve a problem that actually exists or just a figment of your vivid ( & incorrect) imagination?

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631131 is a reply to message #631129]

Fri, 09 January 2015 11:12

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 09 January 2015 17:58

Just use INSERT SELECT maybe with APPEND and/or PARALLEL hint if your hardware and concurrent workload allow them.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631142 is a reply to message #631129]

Fri, 09 January 2015 12:37

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Adding to Michel's correct answer, you could also use CTAS (Create Table As Select) method.
[no smiley]

[Updated on: Fri, 09 January 2015 12:37]

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631160 is a reply to message #631142]

Fri, 09 January 2015 14:54

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

Thanks for reply, Actually table already exists, we have insert/update the records into target table, we do not want to create a table once again. since, its a bulk insert/update. based on limits size 10,00000 (max size) we have to process upto 100,00000 records.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631162 is a reply to message #631160]

Fri, 09 January 2015 15:05

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 09 January 2015 18:12

Michel Cadot wrote on Fri, 09 January 2015 17:58

Just use INSERT SELECT maybe with APPEND and/or PARALLEL hint if your hardware and concurrent workload allow them.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631310 is a reply to message #631162]

Mon, 12 January 2015 10:46

Asfakul
Messages: 43
Registered: July 2014

Member

Use Merge Query.. It's the FASTEST way out there, even faster than BULK Collect in some counts.
Merge into target_table
using ( your SQL Query)
on ( )
WHEN MATCHED THEN
--DO
WHEN NOT MATCHED THEN
-- DO
END

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #631312 is a reply to message #631310]

Mon, 12 January 2015 10:56

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why a MERGE when you just want to INSERT?
I gave the FASTEST way.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #632308 is a reply to message #631312]

Wed, 28 January 2015 15:44

ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA

Member

Agree with Michel Cadot. INSERT/APPEND/PARALLEL/NOLOGGING should suffice.

Report message to a moderator

Re: How to insert with100 Million record's sql query result into another table 1 million by 1 million [message #632309 is a reply to message #632308]

Wed, 28 January 2015 15:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ravikanth_b wrote on Wed, 28 January 2015 13:44

Agree with Michel Cadot. INSERT/APPEND/PARALLEL/NOLOGGING should suffice.

I will sleep better tonight knowing that you agree with Michel.
Should everyone post similar "I AGREE" post to every correct answer to every thread on this forum?

Report message to a moderator

Previous Topic:	unknown procedure or function
Next Topic:	Query taking too long to execute and then throws an error. Need to change the SQL query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:32:19 CDT 2024