Hello everyone...i have a store procedure which has a merge command, merging into tables....The procedure was working pretty fast 10-20 secs for 200000 merge , but sometimes it is slow takes 20 mins and it does this consistently after it becomes slow. I observed that, if i delete the table and recreate the table again, it performs like before...any ideas why this is happening...
store procedure code
BEGIN
-- TABLE 1 --
MERGE /*+ APPEND */ INTO TABLE1 A USING TEMPTABLE B ON (
B.CHILDCOMPONENTTYPE='001')
WHEN MATCHED THEN
UPDATE SET A.ALX=B.PARENTCOMPONENT;
-- TABLE 2 --
MERGE /*+ APPEND */ INTO TABLE2 A USING TEMPTABLE B ON (
A.BNK_NUMBER = B.CHILDCOMPONENT
)
WHEN MATCHED THEN
UPDATE SET A.CIRCUIT_NUMBER = B.PARENTCOMPONENT WHERE B.CHILDCOMPONENTTYPE = '002'
WHEN NOT MATCHED THEN
INSERT(BNK_NUMBER,CIRCUIT_NUMBER)VALUES(B.CHILDCOMPONENT,B.PARENTCOMPONENT) WHERE B.CHILDCOMPONENTTYPE = '002';
-- TABLE3--
MERGE /*+ APPEND */ INTO TABLE3 A USING TEMPTABLE B ON (
A.CHILDCOMPONENTTYPE = B.CHILDCOMPONENTTYPE AND
A.CHILDCOMPONENT = B.CHILDCOMPONENT)
WHEN NOT MATCHED THEN
INSERT CHILDCOMPONENTTYPE,CHILDCOMPONENT,PARENTCOMPONENTTYPE,PARENTCOMPONENT)
VALUES(B.CHILDCOMPONENTTYPE,B.CHILDCOMPONENT,COALESCE(B.PARENTCOMPONENTTYPE,' '),
COALESCE(B.PARENTCOMPONENT,' ')) ;