Home » SQL & PL/SQL » SQL & PL/SQL » How to implement Multi-streaming loading data concurrently (Financial,3,unix/window,oracle 9i)
How to implement Multi-streaming loading data concurrently [message #281255] Fri, 16 November 2007 04:23 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
How to implement Multi-streaming loading data concurrently in many streams to reduce time in loading.

Example:
If there is data for 1000 records, instead of loading data sequentially 1 to 1000.
Load data 1 to 100 in set1 ,load data 101 to 200 in set2 & etc..

If loading of 100 records takes 1 secs, then loading 1000 records in 10 sets will take 5 secs.

Can one facilitate, how make a apporoach for the same....
Re: How to implement Multi-streaming loading data concurrently [message #281266 is a reply to message #281255] Fri, 16 November 2007 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insert statement can take array as parameters.
Loader or external table can use direct (bulk) load.

Regards
Michel
Re: How to implement Multi-streaming loading data concurrently [message #281281 is a reply to message #281266] Fri, 16 November 2007 05:55 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS TABLE OF test1.description%TYPE;

t_id id_type := id_type();
t_description description_type := description_type();
BEGIN

FORALL i IN t_id.first .. t_id.last
INSERT INTO test1 (id, description)
VALUES (t_id(i), t_description(i));

COMMIT;
END;

##########

If i will do like that where is concept of multi-streaming..?
Re: How to implement Multi-streaming loading data concurrently [message #281283 is a reply to message #281281] Fri, 16 November 2007 05:58 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
tapaskmanna wrote on Fri, 16 November 2007 05:55

DECLARE
TYPE id_type IS TABLE OF test1.id%TYPE;
TYPE description_type IS TABLE OF test1.description%TYPE;

t_id id_type := id_type();
t_description description_type := description_type();
BEGIN

FORALL i IN t_id.first .. t_id.last
INSERT INTO test1 (id, description)
VALUES (t_id(i), t_description(i));

COMMIT;
END;

##########

If i will do like that then
How the concept of multi-streaming can be implemented?




Re: How to implement Multi-streaming loading data concurrently [message #281287 is a reply to message #281281] Fri, 16 November 2007 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would say that your best bet is to use a Parallel Direct Path insert

Failing that, try breaking your insert data up into small collections and use DBMS_JOB or DBMS_SCHEDULER to fire off an insert process for each of these sets at the same time.

Splitting the insert over N processes is unlikely to produce a N fold improvement in speed, as your insert processes are likely to have data and index block contention issues with each other.
Re: How to implement Multi-streaming loading data concurrently [message #281290 is a reply to message #281287] Fri, 16 November 2007 06:14 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
JRowBottom,

Thanks to all.

But Actual Process i have to follow is:

Read Data from External Data File using SQL Loader & Load it into Stage Table.After loading into Stage table validation is required for data subject to implementation of multi-streaming concept to upload it to main target table (Using PL/SQL).
Re: How to implement Multi-streaming loading data concurrently [message #281291 is a reply to message #281290] Fri, 16 November 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't load it in a stage table, use external table (unless you have multi-pass on the file and this one is bug)
2/ Depending on your validation pass, you can do it directly in SQL in direct, and possibly parallel, mode
3/ Insertall is bulk operation (don't know what multy-stream means)

Regards
Michel
Re: How to implement Multi-streaming loading data concurrently [message #281292 is a reply to message #281290] Fri, 16 November 2007 06:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So you are trying to improve the performance of your Insert while deliberately avoiding the fastest solution?

Go back to the person who specified this and point out that the fastest solution does not involve Pl/SQL.

Failing that, use Execute Immediate in Pl/Sql to do the Alter Session and INSERT APPEND - that way you get to follow the letter of the pointless restriction whicl still getting the best performance.
Re: How to implement Multi-streaming loading data concurrently [message #281330 is a reply to message #281291] Fri, 16 November 2007 07:34 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member

You valuable thought is correct but:

i) One Dameon Process will load the data into stage table
ii) Now after that my role comes:
Multi- Streaming concept means:
Fragment the total records and insert into table using parallel method .

Re: How to implement Multi-streaming loading data concurrently [message #281332 is a reply to message #281330] Fri, 16 November 2007 07:39 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

i) One Dameon Process will load the data into stage table


Don't do that directly use the file with external table

Quote:

ii) Now after that my role comes:
Multi- Streaming concept means:
Fragment the total records and insert into table using parallel method

Just use "insert select" with append and paralel hints.

Regards
Michel
Previous Topic: database writer process
Next Topic: PLSQL procedure
Goto Forum:
  


Current Time: Fri Dec 02 14:24:19 CST 2016

Total time taken to generate the page: 0.16901 seconds