|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/5/2007 11:14:26 AM
Posts: 13,
Visits: 2
|
|
I need to select few million rows from a table and insert
into an already populated table.
I thought Mload utility would be better than normal Insert select into Query
considering factors like Duplicate row checks that happen etc.
But my DBA feels a normal insert select Query would be efficient than
an Mload.
I would like to know the comments from experts on the same.
Regards,
Annal Tamizhnambi
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
The insert/select should always be better. In order to use Mload, you would first have to export the rows to a host platform, so that would also take some time. The Mload has to also do the duplicate row checking.
If you have a high number of rows per primary index value and you are sure that your process does not create duplicate rows, you may want to make the target table a MULTISET table. This will allow the duplicate row checking to be bypassed.
The only case that I can think of where Mload could beat the insert/select was if the machine restarted causing a rollback to the insert/select. Since Mload does it's own logging, it does not rollback, but will continue where it left off. So, unless you're planning on having the machine restart as part of your process, insert/select would be better.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/5/2007 11:14:26 AM
Posts: 13,
Visits: 2
|
|
In order to use Mload, you would first have to export the rows to a host platform, so that would also take some time. The Mload has to also do the duplicate row checking.
Hi Barry,
Thanks for the information 
But Duplicate row checking would happen even if one uses an Insert statement right??
Can you please throw light on when Mloads are used instead of Inserts?
(Is it only when Rollback happens as you mentioned or even in some other specific cases as well??)
Regards,
Annal Tamizhnambi
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
Yes, duplicate row checking would happen in both cases, unless you specified the table to be MULTISET. In that case, neither the INSERT/SELECT nor Mload would do duplicate row checking. So, they are essentially equivalent from that standpoint.
The only time where I would see Mload would be an advantage is if you had some type of failure (hardware, etc) during the process. An insert/select will do a rollback if there is a failure. So, if you had a database restart while you were doing the insert/select, you would have to wait for the rollback to complete, then it would start all over doing the insert/select again. Mload will simply pick up where it left off after the failure because it maintains a separate work table that keeps track of the changes it's made. So, in this regard, Mload could be better. But I wouldn't design something expecting to have a failure.
If you didn't have some type of failure the insert/select should always outperform the Mload.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/5/2007 11:14:26 AM
Posts: 13,
Visits: 2
|
|
I got the point.
So considering there wont be any failure and the Target Table IS a SET Table,
The reason why Insert is better than Mload is
--> Mload takes time to move the data to a host platform before moving it to a Target table.
Please confirm if i got the reason rite!!!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
I think there are times (especially with large amount of records and you have a Gigabit LAN so your extract/load times are quite high), when MLOAD would be a better approach because of the Transient journaling involved with INSERT/SELECT on a populated table.
You may want to try a sample prototype and pick a methodology that suits you
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/5/2007 11:14:26 AM
Posts: 13,
Visits: 2
|
|
Thanks for the reply!!!
So in normal cases Insert is better than Mload becos
--> Time taken by mload to move data to host platform is FAR MORE than the time taken for Transient journal entry while executing Insert query.
Kindly confirm if this is right!!!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/21/2007 2:00:00 AM
Posts: 4,
Visits: 1
|
|
Insert Select is better in case we have no exceptional data or duplicate record and the structure of target and source tables including PIs are same but if you want to take decisions like which data goes in duplicate and exception and which in normal table then Mload is the best choice.
Farhan Nisar
|
|
|
|