Delete Query -To Improve Performance

Question

Below is the query I wrote, wanted to know if i can improve the performance further.Can any one please help me?

DELETE FROM GLOBAL_TEMP
WHERE EXISTS (
    SELECT GLOBAL_TEMP.ASSET_ID
    FROM  TEMP AEE
      WHERE GLOBAL_TEMP.ASSET_ID = AEE.ID_ASSET 
      AND   GLOBAL_TEMP.TYPE = 'abcdef'
      AND   AEE.id_temp_notation
    IN (SELECT ID FROM TEMP2 WHERE IS_DISPLAYABLE = 'N')
);

It is hard to tune a query without knowing what exactly you want to delete and without having any database information, but looking at the simplicity of the query I doubt it can be improved in any way that it matters. — Jean-François Savard, Jan 19 '15 at 17:45
The "IN" would be slow, a second exists or a join between temp and temp2 would likely be more efficient. also is temp2 id indexed as is is_displayable in temp2? — xQbert, Jan 19 '15 at 17:47
More info needed. Volume of data in GLOBAL_TEMP, TEMP and TEMP2; indexes on relevant columns; execution plan for the delete statement. — Jeffrey Kemp, Jan 20 '15 at 01:25

score 0 · Answer 1 · edited Jan 20 '15 at 01:24

This would likely be a bit more efficient... the in clause could be slow compared to a join or an exists.. also returning 1 (since we're just checking for existence and not values) instead of looking up each value would likely be slightly faster.

DELETE FROM GLOBAL_TEMP
WHERE EXISTS (
    SELECT 1
    FROM  TEMP AEE
    INNER JOIN temp2 t2
     on AEE.ID_temp_notation = t2.id
    WHERE GLOBAL_TEMP.ASSET_ID = AEE.ID_ASSET 
      AND GLOBAL_TEMP.TYPE = 'abcdef'
      and t2.is_Displayable='N');

However, without knowing specific indexes, full table relationships and volume of data along with execution plans, this is a "best guess" based on "IN" typically being slow.

An IN clause can not necessarily be replaced with a JOIN. Plus in nearly all cases where it *can* be replaced Oracle will create identical execution plans. — , Jan 19 '15 at 19:00

Delete Query -To Improve Performance

1 Answers1