Re: Help me tune this query (please).

Department of Justice wrote:
>
> This query is taking AGES to run. Is there a better way to run it?
>
> select a.client_org_id, a.year
> from gab_p.adm_fee_batch a, gab_p.org o
> where a.client_org_id = o.org_id
> AND a.year = '1995/1996'
> AND a.batch_id = 474
> AND o.party_type = 'CL'
> AND nvl(o.org_status,'1') in ('D','E','I')
> /

 ...
> I suspect it's the NVL function, could I use something else to acheive
> the same result

I suspect you're right. Your EXPLAIN shows a bunch of index range scans followed by retrieval of rows by rowid. Without seeing the columns of your indexes I'm just guessing but if you could satisfy some or all selection criteria from indexes alone and get rid of the rowid retrievals you'd probably get a faster response. Mr. Optimizer doesn't like functions, so that NVL is going to go against row data for sure.

Since you don't want any rows with o.org_status of null, I don't think you need to translate the nulls to 'l'. If you get a null it will not be in ('D','E','I'). I suggest letting the nulls just slide on by with:

AND o.org_status in ('D','E','I')

-- 
===============================================
"FROM" is disabled to stop junk mail (spam).
Please reply to:        +----------------------
Paul.Deanguera_at_ci.      |  Paul de Anguera
seattle.wa.us           |  ESD, City of Seattle