Kapitula Alexey Kapitula Alexey - 18 days ago 7
SQL Question

Oracle replace not in by left join

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

Answer

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!

Comments