Re: Oracle Index Question

From: Andy Spaven <andy.spaven_at_eps-hq.co.uk>
Date: Thu, 4 Dec 2003 11:35:38 -0000
Message-ID: <m_Ezb.213$OA.118@newsr2.u-net.net>

<snip>
> Optimser Mode is COST
> Stats are upto date
>
> Oracle Version is 8.1.7.4.0 on Solaris 8
>
> The colums I am interested in is ACCNT_CODE,PERIOD,ANAL_T1 &
> (IORFLM0.L0)
>
> Is it possible to build an index on these colums if so how, I would
> like to see if it makes any diffrance to speed.
>

It's not possible to build a single index on all 4 columns (if that's what you're asking). Nor is it likely to be much use building an index on any of these columns except perhaps ANAL_T1 for your example query. All the others are either just selected (L0) or are in the where clause using a between criteria with what looks to be a very large range of values. Therefore indexing them won't make any difference (probably) as Oracle would see from the stats the the between clause is not going to eliminate many records and so wouldn't bother with the index (usually).

Indexing ANAL_T1 might make a difference if the value A01 (or other values used to match SALFLDBGHF.ANAL_T1) is selective enough (i.e. there aren't more than 5%/10%/15% of the rows matching that value - choose a % based on experience, books you've read etc). Try it and see. The more rows in SAL...GHF the more selective ANAL_T1 needs to be to be worth indexing.

Andy

> Sample query
>

////////////////////////////////////////////////////////////////////////////
//////////////////////

> SELECT IORFLM0.L0, SUM(SALFLDGBHF.AMOUNT), IORFLM2.L2, IORFLM6.L6 FROM
> IORFLM0, SALFLDGBHF, IORFLM2, IORFLM6 WHERE IORFLM0.Le
> dger (+) = 'BHF' AND IORFLM0.TCategory (+) = 'T1' AND
> SALFLDGBHF.ANAL_T1 = IORFLM0.TCode (+) AND SALFLDGBHF.PERIOD BETWEEN
> 20
> 03001 AND 2003008 AND SALFLDGBHF.ACCNT_CODE BETWEEN '1' AND '19000'
> AND SALFLDGBHF.ANAL_T1 = 'A01' AND IORFLM0.L0 BETWEEN '10
> ' AND '79' AND IORFLM2.L2 BETWEEN '198701' AND '200212' AND
> IORFLM2.Ledger (+) = 'BHF' AND IORFLM2.TCategory (+) = 'T1' AND S
> ALFLDGBHF.ANAL_T1 = IORFLM2.TCode (+) AND IORFLM6.L6 BETWEEN ' ' AND
> 'z' AND IORFLM6.Ledger (+) = 'BHF' AND IORFLM6.TCategory
> (+) = 'T1' AND SALFLDGBHF.ANAL_T1 = IORFLM6.TCode (+) AND
> SALFLDGBHF.ROUGH_FLAG <> 'Y' AND SALFLDGBHF.JRNAL_NO <= 0592484 GR
> OUP BY IORFLM0.L0, IORFLM2.L2, IORFLM6.L6
>

////////////////////////////////////////////////////////////////////////////
/////////////////////////////

<snip> Received on Thu Dec 04 2003 - 05:35:38 CST