| Subject | Re: [ib-support] Re: Case Sensitivity on Indices |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-01-16T03:12:29Z |
At 01:57 AM 16/01/2003 +0000, you wrote:
Firebird does, though, do they? With the exception of Oracle, the others
are still playing catch-up to Firebird and InterBase in this regard.
Firebird DOES have an easy solution to implement case-insensitive
indexes: you define the index with a case-insensitive collation order.
The catch is that a case-insensitive collation order has to exist for the
character set you are using. It's a fact that Windows has received better
attention in this regard than has Linux.
Where such collation orders do exist, it's still necessary to use them
conservatively. Generically, they are costly on resources - which is no
less true on DBMSs which implement case-insensitive collation orders
invisibly. The showstopper, pre-Firebird 2, is that c-i-c-o drastically
reduces the horizontal size of the index.
So you can go with the DBMSs that let you blindly define case-insensitive
searches for everything and cop the resulting slow searches; or you can
design your case-insensitive searches with more attention to
performance. Where performance is the issue, there is nothing faster than
an indexed search on a proxy (aka shadow) column.
One would take this approach in Oracle when required. In MySQL you can't
implement the automatic maintenance of proxy columns because there is no
trigger capability. It's fairly uneconomical in MSSQL as well, because its
trigger capability is pretty primitive.
The argument that Firebird should bend its implementation of
case-insensitive indexing to align with inferior and less flexible
implementations in competitor products appears specious to me.
Conversely, the argument that Firebird deserves attention to improving the
spread (platforms, character sets) of available case-insensitive collation
orders, in combination with extending the horizontal size limit of indexes,
appears to be an objective which is both valid and desirable.
heLen
>All major database have the features for case insensivity on indices?Not all databases support the enormous range of character sets that
>Why could not Firebird come with an easy solution to implement it? It
>is a pain to create duplicate fields in order to do fast search using
>index especially considering an application to support all major database?
>
>jw
Firebird does, though, do they? With the exception of Oracle, the others
are still playing catch-up to Firebird and InterBase in this regard.
Firebird DOES have an easy solution to implement case-insensitive
indexes: you define the index with a case-insensitive collation order.
The catch is that a case-insensitive collation order has to exist for the
character set you are using. It's a fact that Windows has received better
attention in this regard than has Linux.
Where such collation orders do exist, it's still necessary to use them
conservatively. Generically, they are costly on resources - which is no
less true on DBMSs which implement case-insensitive collation orders
invisibly. The showstopper, pre-Firebird 2, is that c-i-c-o drastically
reduces the horizontal size of the index.
So you can go with the DBMSs that let you blindly define case-insensitive
searches for everything and cop the resulting slow searches; or you can
design your case-insensitive searches with more attention to
performance. Where performance is the issue, there is nothing faster than
an indexed search on a proxy (aka shadow) column.
One would take this approach in Oracle when required. In MySQL you can't
implement the automatic maintenance of proxy columns because there is no
trigger capability. It's fairly uneconomical in MSSQL as well, because its
trigger capability is pretty primitive.
The argument that Firebird should bend its implementation of
case-insensitive indexing to align with inferior and less flexible
implementations in competitor products appears specious to me.
Conversely, the argument that Firebird deserves attention to improving the
spread (platforms, character sets) of available case-insensitive collation
orders, in combination with extending the horizontal size limit of indexes,
appears to be an objective which is both valid and desirable.
heLen