Re: Using Indexes

On 01-Mär-99 18:09:03 DelphiDev wrote:

>I have submitted the following statements to SQL Plus 3.3 on Oracle
>7.0:

>SELECT IDCODE FROM PERSONEL ORDER BY IDCODE;

>This query takes a fair amount of time. I then index it and run the
>query again:

>CREATE INDEX IDCODEINDEX ON PERSONEL(IDCODE)
>SELECT IDCODE FROM PERSONEL ORDER BY IDCODE;

>The query seems to take the same amount of time to "Order" the records
>before actually printing them. I assume that if a index is present
>then Oracle will take advantage of it. This doesn't seem to be the
>case. Could someone explain this?

Well, I guess that IDCODE is a char or varchar2 column. In this case the index cannot be used for the order by clause because the index is sorted differently from the order the nls_sort parameter suggests. Maybe setting nls_sort to binary will use the index but I haven't tried this.
Annother option is using hints:

select /*+ USE_INDEX(PERSONEL,IDCODEINDEX) */ idcode from personel;

If I remember the sytax correctly, this will give you the result in the order of the index.
But beware, hints are just hints! So if you give the wrong syntax, you will get no error message. Oracle just does not use the right execution plan. So you have to check the plan usin explain plan or set autotrace on.

Using the above hint has the disadvantage that you have to code the name of the index into your statement though.

>Thanks, Stan.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |