Re: Query optimisation

On 18 Nov 1997 23:24:40 GMT, "Geoff Dewhurst" <Geoff_at_Bass-Soft.com.au> wrote:

>Hi,
>
>I am having trouble optimizing a simple statement in Oracle 7.3.3 for NT.
>The primary key on the table is CLRIDX, when I explain plan the following
>statement:
>
> SELECT CLRIDX FROM CLR WHERE CLRIDX = 1;
>
>It uses the index like it should, however, when I change the statement to :
>
> SELECT CODE FROM CLR WHERE CLRIDX = 1;
>
>the index is not used!!! Can anyone explain this? The COST in both cases is
>1 however the second takes longer.
>--
>Geoff
>Geoff_at_Bass-Soft.com.au

There is probably a small number of records in the table (or when the table was last analyzed).

In the first case, the optimiser has the choice of a full table scan, an index scan, or a full index scan. If an index search is used, a block in the table need not be accessed. Given that the index will always be smaller than the table it will choose one of the two index access methods.

In the second case, the choice is between an index search and a table access or a full table scan. The optimiser has decided because of the small number of rows that a full table scan will be faster.

It seems to be not uncommon for the optimiser to choose a full table scan incorrectly. You could re-analyze the table to see if that makes a difference.

Even using an index the second query would be slower than the first because of the need to perform a table access. You could use the index hint to see the performance with an index e.g.

SELECT /*+ INDEX( CLR CLRIDX ) */ CODE FROM CLR WHERE CLRIDX = 1; Received on Wed Nov 19 1997 - 00:00:00 CST