Re: Help me tune this query (please).

It is not using that index because ORG_ID is not part of the where clause. In order for an index to be used, you need to specify at least the first field in the index in the where clause and the field cannot be used with a function or a bunch of other things that will cause the index to be ignored. This is true for the rule based optimizer, not sure is you can use a hint when using cost.

In article <3470D4A9.319DD57E_at_vdoj.vic.gov.au>,   Department of Justice <sysadmin_at_vdoj.vic.gov.au> wrote:
>
> 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

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet