RE: Re: How Oracle use an INDEX?

> Oracle will only use a concatenated index if the index is used in order.
>
> For example.
>
> Index on blah(blah1, blah2, blah3)
>
> If you do not have blah1 in your where clause predicate, the index will
> not be used, and it will look for another usable index or do a full
> tablescan.
>
> Then again, you do a where blah1, blah3.
> The index will be used, but only for the blah1 column as the blah2 column
> was not used.
> To use a column in a concatenated index, you must use all previous columns
> in the predicate.
> As long as you use at least the first column, your index will be used to
> some extent.
>
> Good thing to know is always chose wisely when using concatenated indexes,
> they can prove very beneficial for performance (especially when using
> compression on them, 8.1.6+ I believe) but unless you place them in the
> correct order on good choice of columns, they can prove to cause a lot of
> full tablescans. Always order concatenated indexes in order of which
> columns are used most often.
>
> Proper use of concatenated indexes takes a little getting used to, but
> they perform much better than single non-unique columns and sometimes many
> times better.
>
> HTH,
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
> You Wrote:
>
> Hello All, Suppose there is an index on table policy as create unique
> index on policy as (companycode,org_brachcode,branchcode,
> polseq,endcount); Now do I write like this select * from policy where
> companycode = 'T' and org_branchcode = '01' and branchcode = '02' and
> polseqnos = 134234 and endcount = 0; OR select * from policy where
> endcount = 0 and polseqnos = 134234 and branchcode = '02' and
> org_branchcode = '01' and companycode = 'T'; I think both will use the
> index...so do the explain plan show using TOAD. Oracle do a full table
> scan it first index column is not used. Could GURUs here please comment.
> TIA and Best Regards, Nadeem Kafi.
>
>

-- 
Posted from [140.186.159.3] 
via Mailgate.ORG Server - http://www.Mailgate.ORG