Tuesday 14 April 2015

using MERGE statement and getting ORA-30926: unable to get a stable set of rows in the source tables

This was a pain, but at the end of the day quite simple to solve.  I had a large MERGE statement (I’d moved media objects to separate folders based upon change date) and needed to update these in bulk in the F00165 (2 million + attachments!)

My merge statement kept failing with the error above and I was scratching my head about what I’d done wrong.  I used a script to populate F00165SRM (as seen in previous post) and then I used this as my master list to update the F00165 from.  It was nice and simple.  What I did not realise is that in pursuit of performance (damn me), I multithreaded the cr@p out of the script that populated F00165SRM, I launched a heap of them to try and catch up (under the pump again), I’d inadvertently launched a script twice, so I had a shed load of dupes in F00165SRM.  this is an “essentially insert only” table, so I did not want to slow things down with uniqueness based PKs!  So the reason I was getting ORA-30926 was because I had heaps of duplicates in the F00165SRM! 

A quick script to deduplicate the table: (based upon)

 select count(1), mofilename
from proddta.f00165srm
group by mofilename
having count(1) > 1 ;

delete from proddta.f00165srm
where (rowid, mofilename) in (
select max(rowid), mofilename
from proddta.f00165srm
group by mofilename
having count(1) > 1);
commit;

First one to identify and second to delete (500000 rows – whoops).  I was then able to run the merge successfully:

MERGE
INTO proddta.f00165 t3
USING (
SELECT t1.rowid AS rid, replace(t2.mofilename, 'Z:\','\\jdedatastore\JDEMediaObjects\MediaObjectsData\') as newMOLocation
FROM proddta.f00165 t1
JOIN proddta.f00165srm t2
ON substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)))
WHERE instr(mofilename,'\',-1,1) > 1
and instr(gdgtfilenm,'\',-1,1) > 1
and length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 5
and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 5
and gdgtmotype = '5')
ON (t3.rowid = rid)
WHEN MATCHED THEN
UPDATE
SET t3.gdgtfilenm = newMOLocation;
commit;
That is very nice.

image


My statement is called an UPSERT – how cool is that!



Closing the loop on this:


Once again I had the ORA-30926 after cleaning up what I thought were all the duplicates, but there were more.


What I found was that you need to use the “JOIN” clause for the source table, this needs to be unique.  Therefore the real test of duplicates was

  select count(1), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
from proddta.f00165srm
group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
having count(1) > 1 ;

 


Notice how I’m not checking the entire field, just the bit that my JOIN was joining on.  I was then able to identify all of the offending records and delete them from the F00165SRM.  Which this is once again a slightly more complicated version of the above example of de-duplication.

DELETE from proddta.F00165SRM 
where (rowid, substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))) in (
select min(rowid), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
from proddta.f00165srm
group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
having count(1) > 1);

No comments: