Re: I/O performance problem

> Ok, my query is:
>
> select distinct a.serditta, upper(a.codprovincia),
> upper(a.nummeccanog), upper(a.denominazione),
> upper(a.capoperativo), upper(a.comuneoperativo),
> upper(a.provinciaoperativo), upper(a.statoazienda),
> upper(a.flagvalidazione), upper(a.flagcesdir),
> upper(a.flagcessazione)
> from ditte a ,prodotti b
> where upper(a.classefatturato) like 'C' and
> upper(a.classeaddetti) like 'B' and
> ( upper(a.provinciaoperativo) like 'MI')
> and b.serditta = a.serditta and
> ( upper(b.desprodotto) like '%CARNE%' ) and
> b.impexp like 'I'
> UNION
> select distinct aa.serditta, upper(aa.codprovincia),
> upper(aa.nummeccanog), upper(aa.denominazione),
> upper(aa.capoperativo), upper(aa.comuneoperativo),
> upper(aa.provinciaoperativo), upper(aa.statoazienda),
> upper(aa.flagvalidazione), upper(aa.flagcesdir),
> upper(aa.flagcessazione)
> from dittemi aa ,prodotti bb
> where upper(aa.classefatturato) like 'C' and
> upper(aa.classeaddetti) like 'B' and
> ( upper(aa.provinciaoperativo) like 'MI') and
> bb.serditta = aa.serditta and
> ( upper(bb.desprodotto) like '%CARNE%' ) and
> bb.impexp like 'I'
> order by 4
>

Hi Giuseppe

The first thing that strikes me about this query is that using the UPPER() function when comparing the fields this will stop ORACLE from using any indexes you have created. To make this work you have to add columns to the table which are uppercase versions of the existing columns and index on them.

Also when using LIKE it only works properly if you include a meta character like '%' or '_' so

upper(aa.classefatturato) like 'C'

is the same as

upper(aa.classefatturato) = 'C'

though I don't know how or if this affects performance.

Finally, I think I read in an earlier posting to this group that LIKE '%xx%' is quite costly in terms of performance.

Hope this helps.

Jason Received on Thu Apr 30 1998 - 09:31:07 CDT