Re: Help me tune this query (please).

From: Department of Justice <sysadmin_at_vdoj.vic.gov.au>
Date: 1997/11/18
Message-ID: <3470D4A9.319DD57E@vdoj.vic.gov.au>#1/1

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

Sorry, forgot to put the indexes.
I've narrowed the problem down to this part of the query:

explain plan set statement_id='FEE' for
select org_id,party_type
from gab_p.org
where party_type='CL'
/

QUERY_PLAN

---

 SELECT STATEMENT Cost =
  2.1 TABLE ACCESS BY ROWID ORG
    3.1 INDEX RANGE SCAN ORG_NDX_5 NON-UNIQUE 1.1 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 6 rows selected.

These are (2 of) the indexes on the table, why isn't it using the ORG_PK index?

GAB_P    ORG                  ORG_PK                    ORG_ID
GAB_P    ORG                  ORG_PK                    PARTY_TYPE
GAB_P    ORG                  ORG_NDX_5            SOUNDEX_PHONETIC_CODE

GAB_P    ORG                  ORG_NDX_5             PARTY_TYPE

Thanks,

Andy Received on Tue Nov 18 1997 - 00:00:00 CST