Oracle replace not in by left join

Question

i'm trying to create a cleaning script that will purge all unused descriptions: My query looks like:

DELETE FROM DESCRIPCIONES WHERE ID_DESCRIPCION NOT IN
(SELECT ID_NOMBRE FROM CUESTIONARIOS 
UNION
SELECT ID_DESCRIPCION FROM CUESTIONARIOS 
UNION
SELECT ID_NOMBRE FROM PLANTILLAS
....
)

Also i have much more UNIONs in this query. This is why this query takes too much time. Is there a way to replace this query by faster LEFT JOIN and avoid nesting query. PD: Oracle 11g

Thanks

You'd get some performance benefit by replacing all those unions with union all — Boneist, Nov 11 '16 at 15:41
@KapitulaAlexey I see a lot of SQL-related questions with all-caps titles. I'm just curious about why that is. — Biffen, Nov 11 '16 at 15:41
`ID_DESCRIPTION NOT IN ID_NOMBRE` is descripton and name something you're going to compare? Or subquery should contain only selects for `ID_DESCRIPTION`. Also as Boneist said use `union all` instead of `union` and you should get result much faster. — Kacper, Nov 11 '16 at 16:16
I removed the DBMS specific tags because it is unclear which DBMS you are using and the answers will not be the same. Feel free to add back the DBMS that you actually use. — Sean Lange, Nov 11 '16 at 17:42

score 0 · Accepted Answer · answered Nov 14 '16 at 11:35

Finally i used next query:

DELETE FROM DESCRIPCIONES WHERE ID_DESCRIPCION NOT IN
(SELECT ID_NOMBRE FROM CUESTIONARIOS 
UNION ALL
SELECT ID_DESCRIPCION FROM CUESTIONARIOS 
UNION ALL
SELECT ID_NOMBRE FROM PLANTILLAS
....
)

and have got a huge performance improvement, thanks to all!

Oracle replace not in by left join

1 Answers1