Poor performance in tables join

Question

I'm experiencing very poor performance in a Oracle SQL query. The query is this:

 SELECT distinct idm3.cod
    FROM ibe_partos_m idm3, ibe_padron_prov_2010_m pad2
   WHERE     idm3.codLote = 1
         AND idm3.activo = 1
         AND ((pad2.ar = '2016' and pad2.mes='1') or (pad2.ar = '2015' and pad2.mes='7'))
         AND idm3.cod NOT IN                
           (SELECT idm2.cod
              FROM ibe_partos_m idm2,
                   ibe_padron_prov_2010_m pad
             WHERE     idm2.codLote = 1
                   AND idm2.activo = 1
                   AND ((pad.ar = '2016' and pad.mes='1') or (pad.ar = '2015' and pad.mes='7'))
                   AND pad.tiden != '2'
                   AND idm2.nombreM = pad.NOMB
                   AND idm2.apell1m = pad.APE1
                   AND idm2.apell2m = pad.APE2
                   AND (    idm2.numdocm = pad.IDEN || pad.LIDEN OR 
                                idm2.numdocm = pad.NDOCU OR 
                                idm2.numdocm = pad.LEXTR|| pad.IDEN|| pad.LIDEN OR 
                                idm2.numdocm = pad.LEXTR || '0' || pad.IDEN|| pad.LIDEN OR 
                                idm2.numdocm = pad.lextr || SUBSTR (pad.iden, 2, LENGTH (pad.iden))|| pad.liden)
                )                        
         AND idm3.PROREM = '07'
         AND idm3.nombreM = pad2.nomb
         AND idm3.apell1m = pad2.ape1
         AND idm3.apell2m = pad2.ape2
         AND (   (pad2.tiden = '1' AND pad2.liden IS NOT NULL)
              OR (    pad2.tiden = '3'
                  AND pad2.liden IS NOT NULL
                  AND pad2.lextr IS NOT NULL));

I've indexes defined in table ibe_partos_m, field codlote and field cod; and in table ibe_padron_prov_2010_m, fields ape1, ape2 and iden. All indexes are simple.

I can't understand why I'm having that poor performance...isn't the 2 indexes in ape1 and ape2 suffice for improving the join velocity?

Thank you in advance!!

Edit: what I'm trying to achieve is this:

• Let's define the records that are correct as the records selected in the inner select.

• I'm trying to get the records that are not correct in the former sense, but still fits into some properties, that are:

  idm3.codLote = 1
       AND idm3.activo = 1
       AND ((pad2.ar = '2016' and pad2.mes='1') or (pad2.ar = '2015' and pad2.mes='7'))
  
  
  
  AND idm3.PROREM = '07'
       AND idm3.nombreM = pad2.nomb
       AND idm3.apell1m = pad2.ape1
       AND idm3.apell2m = pad2.ape2
       AND (   (pad2.tiden = '1' AND pad2.liden IS NOT NULL)
            OR (    pad2.tiden = '3'
                AND pad2.liden IS NOT NULL
                AND pad2.lextr IS NOT NULL));
  

Edit2: As @Craig Young correctly suspects, I'm only interested (in both selects) in obtaining distinct cod...but is there some way to tell the DB server to stop searching for a given ibe_partos_m once a match is found in some or both selects?

Answer 1

Alarm bells are screaming at:

SELECT distinct idm3.cod

You're probably using DISTINCT to hide duplicates because of a joining error in your query. If you change that to the following you'll probably see that you're generating a huge number of rows only to hash out the duplicates:

SELECT COUNT(idm3.cod)

We don't know your schema, so we cannot point out your mistake. But you can start by checking appropriate filtering of the PK fields of each of your tables.

I also suggest follow Leketo's advice and use explicit JOIN syntax. It makes joining errors much easier to identify.

---

I also just noticed you're using many string-numbers.

• If the underlying fields are char/varchar: your table design is inefficient. It can be significantly improved by changing those columns to appropriate numeric types.
• If the underlying fields are already numeric types: using string-literal filters is probably resulting in non-sargable queries (i.e. you're unable to leverage indexes). Start using correct types and you may see significant improvement.

Answer 2

Using JOIN makes the code easier to read, since it's self-explanatory.

LEFT JOIN is guaranteed to return every row from idm2

and adm2.cod is null;

select distinct idm3.cod
  from ibe_partos_m idm3

  inner join ibe_padron_prov_2010_m pad2
     on idm3.nombrem = pad2.nomb
     and idm3.apell1m = pad2.ape1
     and idm3.apell2m = pad2.ape2

  left join (select idm2.cod
          from ibe_partos_m idm2
          inner join ibe_padron_prov_2010_m pad
           on idm2.nombrem = pad.nomb
           and idm2.apell1m = pad.ape1
           and idm2.apell2m = pad.ape2  
         where idm2.codlote = 1
           and idm2.activo = 1
           and ((pad.ar = '2016' and pad.mes = '1') or
               (pad.ar = '2015' and pad.mes = '7'))
           and pad.tiden != '2'         
           and (idm2.numdocm = pad.iden || pad.liden or
               idm2.numdocm = pad.ndocu or
               idm2.numdocm = pad.lextr || pad.iden || pad.liden or
               idm2.numdocm = pad.lextr || '0' || pad.iden || pad.liden or
               idm2.numdocm =
               pad.lextr || substr(pad.iden, 2, length(pad.iden)) ||
               pad.liden))  idm2
   on idm3.cod = idm2.cod   

 where idm3.codlote = 1
   and idm3.activo = 1
   and ((pad2.ar = '2016' and pad2.mes = '1') or
       (pad2.ar = '2015' and pad2.mes = '7'))
   and idm3.prorem = '07'
   and ((pad2.tiden = '1' and pad2.liden is not null) or
       (pad2.tiden = '3' and pad2.liden is not null and
       pad2.lextr is not null))
    and adm2.cod is null;