Re: How Oracle use an INDEX?

On 15 Aug 2001 06:17:49 -0700, kafis_at_super.net.pk (Nadeem Kafi) 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.

First of all, it would have been useful if you would have posted info on

- version
- platform
- which optimizer you are using

Leaving this info out makes it very difficult to provide a specific answer, one more or less has to guess.

Of course it is quite natural Oracle performs a full table scan if the leading column of an index doesn't occur in your where clause. Do you think Oracle will simply use all values in the leading column and run <n> 'sub' queries, where <n> is the number of distinct values in the leading column.
Using the rule based optimizer that has simply *never* been the case, and using the cost based optimizer such an approach would cost more than conducting a full table scan, which is exactly why Oracle performs a full table scan
There is a note on Metalink for this issue, not using the leading column of an index is one of the first issues they address The note id is 65722.1

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Aug 15 2001 - 14:16:48 CDT