gusta1308 escreveu:

> 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.
>

I didn't understood the two timings info, as you showed three queries.

Do you mean MERGE and INSERT+UPDATE are both slow or only MERGE?


Adriano