Using UUIDs and Oracle's NLS_COMP & NLS_SORT settings

开发者_JAVA百科My tables use UUIDs as primary keys and store them in an Oracle DB as CHAR(36). Most of the tables contain NVARCHAR columns that could be in any language. I would like to support the natural language sort on these columns and I do that by setting NLS_SORT and NLS_COMP on the oracle session (via ALTER SESSION). The problem I'm hitting is that oracle will not use the binary indexes on the UUID columns and always do full table scans.

Is there anyway to get the sorting goodness without loosing the binary indexes ? One solution I found is using RAW(16) to represent UUIDs, in that case Oracle will use the binary indexes regardless of the NLS sort/comp. But I was hoping there was a better alternative.

Any suggestions?

---

Can you describe the environment a bit more.

If you are storing different languages in the same column, then any form of linguistic sorting is going to be hampered (ie you have a mix of French and German, do you sort in the 'French' order or 'German' order) ?

Also, why use NVARCHARs ? If you have a multi-byte characterset as the default, then VARCHAR will store any necessary characters.

A RAW(16) is obviously a lot smaller than a CHAR(36), and is much closer to the 'native' format of a UUID (though a number could also work). The hex form is more a presentation issue, and isn't something I'd use as a PK (especially including the hyphens). I MIGHT derive it a view or virtual (derived) column in 11gR2.

Especially in a multi-lingual application, there's the risk of character set translation when storing a UUID in a CHAR. I'm not even sure how a UUID should look in Korean or Chinese where letters like 'a','b' and 'c' aren't native.

---

If you are setting the NLS_SORT and NLS_COMP for every session, do you really need the indexes to use the default sorting? Could you create a function-based index instead that implemented the linguistic sorting?