How to implement Multi-streaming loading data concurrently [message #281255] |
Fri, 16 November 2007 04:23  |
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 #281281 is a reply to message #281266] |
Fri, 16 November 2007 05:55   |
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   |
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   |
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   |
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 #281292 is a reply to message #281290] |
Fri, 16 November 2007 06:19   |
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 #281332 is a reply to message #281330] |
Fri, 16 November 2007 07:39  |
 |
Michel Cadot
Messages: 68761 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|