Re: Query optimisation

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/11/19
Message-ID: <64u2k5$5t4@bgtnsc02.worldnet.att.net>#1/1

For the second query, ORACLE uses the index on CLRIDX to retrieve the ROWIDs, then accesses the CLR table using the ROWIDs to retrieve the values of CODE. For the first query, it did not need to go to the table, since all the values of CLRIDX are stored in the index.

Hope this helps.

Michael Serbanescu.

---

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
Received on Wed Nov 19 1997 - 00:00:00 CST