Re: USING INDEXES

Mark Ragan wrote at 15-Nov-99 14:54:17 on the subject USING INDEXES:

>I have a table which contains many fields, one of which is "OrderNo".

>This "OrderNo" WILL have duplicate entries.

>I have created an index on this field.

>When doing a "Select * from Filename ORDER BY OrderNo" Oracle sorts
>each record before outputting.

>How can I force Oracle to use the index?

>Why doesn't it use this index by default?.

Hello Mark,
I guess your OrderNo is a varchar2 or a char. Oracle seems to use indexes for sorting only if used on numeric values. Maybe also for date columns. I think the reason for not using indexes on char fields is the nls_sort mechanism. The index is organized by the binary internal order of the characters while the order by lists them in the order defined in your language sort scheme.
So, for example, the german sort order wants the numbers *after* the letters but if you do manual comparisons it is '9'<'A'.

It may be worth a try to set the nls_Sort parameter to binary. I don't remember the exact command or init file parameter so look into the manual.

BTW, I experienced this behaviour with Oracle7 but I think it has not changed with 8 or 8i.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |