The following statement is very slow even thought all tables involve
in this operation have the correct indexes

MERGE INTO TM_HIERARCHIES Des
USING (
select xte.*, ori.HIERARCHY_ID as HierarchyFatherID from
TT_TM_HIERARCHIES xte
inner join TM_HIERARCHIES Ori on (xte.HierarchyFatherCode =
Ori.HIERARCYCODE)
) ext
on (ext.HIERARCYCODE = des.HIERARCYCODE)
WHEN MATCHED THEN
UPDATE SET
Hierarchy = ext.HIERARCHY,
HierarchyLevel = ext.HierarchyLevel,
HierarchyFatherID = ext.HierarchyFatherID
WHEN NOT MATCHED THEN
INSERT (Hierarchy_Id, HierarcyCode, Hierarchy, HierarchyLevel,
HierarchyFatherID)
VALUES (NEXT VALUE for GEN_HIERARCHY_ID, ext.HIERARCYCODE,
ext.HIERARCHY, ext.HierarchyLevel, Ext.HierarchyFatherID)

Plan for statement execute:
PLAN JOIN (JOIN (EXT XTE NATURAL, EXT ORI INDEX (IDX_TM_HIERARCHIES)),
DES INDEX (IDX_TM_HIERARCHIES))


Another way to do this is:
INSERT into TM_HIERARCHIES (Hierarchy_Id, HierarcyCode, Hierarchy,
HierarchyLevel, HierarchyFatherID)
select NEXT VALUE for GEN_HIERARCHY_ID, xte.HIERARCYCODE,
xte.HIERARCHY, 8, ori.Hierarchy_ID from externa xte
inner join TM_HIERARCHIES Ori on (xte.HierarchyFatherCode =
Ori.HIERARCYCODE)
left join TM_HIERARCHIES des on (des.HIERARCYCODE =
xte.HierarcyCode) and (xte.HierarchyLevel = 8)
where xte.HierarcyCode is null

UPDATE TM_HIERARCHIES SET
Hierarchy = (SELECT FIRST 1 HIE.HIERARCHY FROM TT_TM_HIERARCHIES HIE
WHERE TM_HIERARCHIES.HIERARCYCODE = HIE.HIERARCYCODE),
HierarchyLevel = (SELECT FIRST 1 HIE.HIERARCHYLEVEL FROM
TT_TM_HIERARCHIES HIE WHERE TM_HIERARCHIES.HIERARCYCODE =
HIE.HIERARCYCODE),
HierarchyFatherID = (
SELECT FIRST 1 HORI.HIERARCHY_ID
FROM TT_TM_HIERARCHIES HIE
LEFT JOIN TM_HIERARCHIES HORI ON (HIE.HIERARCHYFATHERCODE =
HORI.HIERARCYCODE)
WHERE TM_HIERARCHIES.HIERARCYCODE = HIE.HIERARCHYFATHERCODE
)
WHERE
EXISTS(
SELECT 1 FROM TT_TM_HIERARCHIES HIE WHERE
TM_HIERARCHIES.HIERARCYCODE = HIE.HIERARCYCODE
)

The timings of this execution is 10 min 42 seg for the first statement
and 42 seg for the second statement, having 390000 rows in the tables

I would like to reduce the execution time and code to about 1 min.


PLEASE, HELP ME!!!!!!!!!!!
Thanks in advance