Re: An idea on faster CHAR field indexing

Giles,

I'm curious as to why the need for multiple passes. Is that true even in Latin 1 code pages? If not, this optimization

could at least be used for code pages that don't require multiple passes.

As for memory usage: I don't see the issue here. The translation to some collation sequence has to be done anyhow. 
Writing one's own routine to do look-ups into a collation sequence table is a fairly 
trivial exercise. 

One would have the option with SBCS code pages to either translate to 8 bit collation values or to translate them into

master Unicode collation values. Not sure what the advantage would be of doing the 
latter. I only see it as useful if you have different rows storing text in different code pages and then only if the
RDBMS
 
can know for a given field on a per row basis what its code page is.

On Thu, 22 Jun 2000 06:59:06 +1000, Giles Lean wrote:

>
>> So let me cut to the chase: I'm thinking that rather than store the
>> actual character sequence of each field (or some subset of a field)
>> in an index why not translate the characters into their collation
>> sequence values and store _those_ in the index?
>
>This is not an obvious win, since:
>
>1. some collations rules require multiple passes over the data
>
>2. POSIX strxfrm() will convert strings of characters to a form that
>   can be compared by strcmp() [i.e. single pass] but tends to greatly
>   increase memory requirements
>
>   I've only data for one implementation of strxfrm(), but the memory
>   usage startled me.  In my application it was faster to use
>   strcoll() directly for collation than to pre-expand the data with
>   strxfrm().
>
>Regards,
>
>Giles
>