troubleshooting Question

Conversion from "NOT IN", "IN" ....... to "NOT EXISTS", "EXISTS".

Avatar of CRISTIANO_CORRADI
CRISTIANO_CORRADI asked on
Oracle DatabaseSQL
5 Comments1 Solution1193 ViewsLast Modified:
In order to optimize the query, I tried to rewrite the following query:

   UPDATE STRUCTURE x
            SET (x.ui_res_calcolate, x.ui_bus_calcolate,
                 x.ui_pesate_calcolate, x.coppie_attestate,
                 x.coppie_impegnate, x.res, x.bus, x.td, x.cdn,
                 x.linea_pronta, x.linea_aggiuntiva, x.altro, x.desc_via,
                 x.nome_ra, x.accessibility) =
                   (SELECT a.nui_res, a.nui_bus, ROUND (a.nui_pesate),
                           a.coppie_attestate, a.coppie_impegnate, a.res,
                           a.bus, a.td, a.cdn, a.linea_pronta,
                           a.linea_aggiuntiva, a.altro,
                              TRIM (a.particella_distributore)
                           || ' '
                           || TRIM (a.via_distributore)
                           || ', '
                           || TRIM (a.civico_distributore),
                           b.codice_distributore, a.accessibilita
                      FROM planning_distributori_ext a, planning_sas_centrali b
                     WHERE UPPER (a.adc) IS NOT NULL
                       AND UPPER (a.adc) NOT IN
                                         ('DISTRIBUTORE', 'AREA DI CENTRALE')
                       AND b.codice_distributore =
                                                a.adc || '_' || a.distributore
                       AND b.objectid_ne = x.objectid)
          WHERE x.objectid IN (
                   SELECT b.objectid_ne
                     FROM planning_distributori_ext a, planning_sas_centrali b
                    WHERE UPPER (a.adc) IS NOT NULL
                      AND UPPER (a.adc) NOT IN
                                         ('DISTRIBUTORE', 'AREA DI CENTRALE')
                      AND b.codice_distributore =
                                                a.adc || '_' || a.distributore);

.... in this way:

UPDATE STRUCTURE x
   SET (x.ui_res_calcolate, x.ui_bus_calcolate, x.ui_pesate_calcolate,
        x.coppie_attestate, x.coppie_impegnate, x.res, x.bus, x.td, x.cdn,
        x.linea_pronta, x.linea_aggiuntiva, x.altro, x.desc_via, x.nome_ra,
        x.accessibility) =
          (SELECT a.nui_res, a.nui_bus, ROUND (a.nui_pesate),
                  a.coppie_attestate, a.coppie_impegnate, a.res, a.bus, a.td,
                  a.cdn, a.linea_pronta, a.linea_aggiuntiva, a.altro,
                     TRIM (a.particella_distributore)
                  || ' '
                  || TRIM (a.via_distributore)
                  || ', '
                  || TRIM (a.civico_distributore),
                  b.codice_distributore, a.accessibilita
             FROM planning_distributori_ext a, planning_sas_centrali b
            WHERE a.adc IS NOT NULL
              AND UPPER (a.adc) != 'AREA DI CENTRALE'
              AND UPPER (a.adc) != 'DISTRIBUTORE'
              AND b.codice_distributore = a.adc || '_' || a.distributore
              AND b.objectid_ne = x.objectid)
 WHERE EXISTS (
          SELECT 'X'
            FROM planning_distributori_ext alpha, planning_sas_centrali beta
           WHERE alpha.adc IS NOT NULL
             AND UPPER (alpha.adc) != 'AREA DI CENTRALE'
             AND UPPER (alpha.adc) != 'DISTRIBUTORE'
             AND beta.codice_distributore =
                                        alpha.adc || '_' || alpha.distributore
             AND beta.objectid_ne = x.objectid);

Could you help me to understand if these two queries have the same result set?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros