Index question (NLS_SORT)

From: Jochen Erwied <mack_at_joker.e.ruhr.de>
Date: 1996/09/26
Message-ID: <52eru5$k2l_at_wesel.mh.ruhr.de>#1/1


Hi!

Maybe someone out there can help me solving the following problems:

I'm developing an application which should display a table according to a number of (predefined) sort-orders.

This means I issue the following statements:

Option 1 -> select field1, field2 [...] order by field1, field2
Option 2 -> select field1, field2 [...] order by field1, field3
Option 3 -> select field1, field2 [...] order by field2, field3

and so on.

My problem is: field1 may contain 'Umlauts', i. e. is not totally ASCII. But Oracle will use indices only if NLS_SORT is set to BINARY. Is there a possibility to create indices which are correct under NLS_SORT set to xGerman, and which are automagically used by Oracle?

Oracle Germany told me to use pre-sorted temporary tables. Stone-age data-processing I'd say... especially since the actual table is not view-only, but should be edited and redisplayed as fast as possible.

The only idea I currently have is defining an additional field containing an ASCII-fied representation of the field to sort and create the index on this field. But I'd like to avoid this - one could expect from a product costing thousands of dollars to be able to correctly support NLS.

If you have any questions (since English is not my native language I would expect this...) drop me a mail, or followup to this article.

Any help would greatly be appreciated.

-Jochen
--

Jochen Erwied     |  Internet: mack_at_joker.e.ruhr.de
Knuefen 61        |           Voice: +49-208-995518
D-45475 Muelheim  |    ISDN/V34/FAX: +49-208-995519
Received on Thu Sep 26 1996 - 00:00:00 CEST

Original text of this message