Re: Help me tune this query (please).

I don't have the exact number of records you have for each table, and don't have enough details on where the indices are ....

As far as I know Function such as NVL does not make use of indices for that column.

But you could try this and Inform me how it works ... Good luck ....

since you only need values comming from table a then

select a.client_org_id, a.year
 from gab_p.adm_fee_batch a
 where a.year = '1995/1996'
 AND a.batch_id = 474
 and exists(

      select 'x'
      from  gab_p.org o
      where a.client_org_id = o.org_id
      AND   o.party_type = 'CL'
      AND   nvl(o.org_status,'1') in ('D','E','I'))

make sure you index o.party_type, o.org_id

                    a.year, a.batch_id

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')
> /
>
> EXPLAIN PLAN OUTPUT:
>
> QUERY_PLAN
> ------------------------------------------------------
> SELECT STATEMENT Cost =
> 2.1 NESTED LOOPS
> 3.1 TABLE ACCESS BY ROWID ADM_FEE_BATCH
> 4.1 INDEX RANGE SCAN ADM_FEE_BATCH_N2 NON-UNIQUE
> 3.2 TABLE ACCESS BY ROWID ORG
> 4.1 INDEX RANGE SCAN ORG_NDX_5 NON-UNIQUE
> 1.1 NESTED LOOPS
> 2.1 TABLE ACCESS BY ROWID ADM_FEE_BATCH
> 3.1 INDEX RANGE SCAN ADM_FEE_BATCH_N2 NON-UNIQUE
> 2.2 TABLE ACCESS BY ROWID ORG
> 3.1 INDEX RANGE SCAN ORG_NDX_5 NON-UNIQUE
>
> QUERY_PLAN
> ------------------------------------------------------
> 1.1 TABLE ACCESS BY ROWID ADM_FEE_BATCH
> 2.1 INDEX RANGE SCAN ADM_FEE_BATCH_N2 NON-UNIQUE
> 1.1 INDEX RANGE SCAN ADM_FEE_BATCH_N2 NON-UNIQUE
> 1.2 TABLE ACCESS BY ROWID ORG
> 2.1 INDEX RANGE SCAN ORG_NDX_5 NON-UNIQUE
> 1.1 INDEX RANGE SCAN ORG_NDX_5 NON-UNIQUE
>
> TKPROF OUTPUT:
>
> 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')
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.02 0.01 0 0
> 0 0
> Execute 1 0.00 0.00 0 0
> 0 0
> Fetch 3 1529.18 1755.06 2103090 9104432
> 0 39
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 5 1529.20 1755.07 2103090 9104432
> 0 39
>
> I suspect it's the NVL function, could I use something else to acheive
> the same result
> (but quicker)?
>
> Thanks,
>
> Andy Horne

-- 
================================================================
============================
| Joseph Sumalbag                                                                           
|
| Oracle DBA                                                                                
|
|                                                                                           
|
| The opinions expressed above are my own and doesn't 
 necessarily                           |
|reflect the opinion of any of my client company or my employer.                            
|
================================================================
============================