Optimize a 4 table related oracle query

Question

Hi al,

I need to optimize the following query but 'm not able to wonder how.

select distinct  v.codvia,
    v.codproine,
    v.codmunine,
    tv.SIMBOLO as SIMBOLO_TIPO_VIA,
    tv.NOMBRE as TIPO_VIA,
    c.nombrevia as NOMBRE_VIA,
    v.cp,
    m.nombre as NOMBRE_MUNICIPIO ,
    pr.nombre as NOMBRE_PROVINCIA   
    from tdinumvias v, tdimunicipio m, tdivia c, cditipovia tv, tdiprovincia pr
where (pr.codine = v.codproine) and  
          (m.codproine = v.codproine and m.codine = v.codmunine) and
      (c.codproine = v.codproine and c.codmunine = v.codmunine and
       c.codvia=v.codvia and tv.idtipovia=c.idtipovia) 

there are indexes created for: v.codproine, v.codvia, v.codmunine, c.codmunine, pr.codine, m.codine, c.codproine, v.idtipovia and c.idtipovia

In it's correspondent tables but the performance is still really bad.

---

Added from comments : Table sizes are 11M in tdinumvias, 10K in tdimunicipio, 970K in tdivia the others only have a few rows.

It takes a bit less than a second and i was wondering if it was possible to get it to 100 - 200 milis.

Update:

Finally we have created a new intermediate table with cp, codproine and codmunie precalculated and compiled as a view, then make the first query get data from this view, this gets the query in about 300-400 Millis . it's not as good as we wanted but it's okay.

Thanks

Answer 1

I re-wrote using ANSI-92 syntax, which won't provide any performance benefit aside from readability:

SELECT DISTINCT v.codvia,
       v.codproine,
       v.codmunine,
       tv.SIMBOLO as SIMBOLO_TIPO_VIA,
       tv.NOMBRE as TIPO_VIA,
       c.nombrevia as NOMBRE_VIA,
       v.cp,
       m.nombre as NOMBRE_MUNICIPIO ,
       pr.nombre as NOMBRE_PROVINCIA   
  FROM tdinumvias v
  JOIN tdimunicipio m ON m.codproine = v.codproine 
                     AND m.codine = v.codmunine
  JOIN tdivia c ON c.codproine = v.codproine 
               AND c.codmunine = v.codmunine
               AND c.codvia = v.codvia
  JOIN cditipovia tv ON tv.idtipovia = c.idtipovia
  JOIN tdiprovincia pr ON pr.codine = v.codproine

Review your JOINs - they are what is creating the need for the DISTINCT. At least one JOIN needs to be converted into an IN or EXISTS clause to get rid of the duplicates.