Sven K�hler wrote:
Because it's slow? (well for anything other than *very* short strings)I could imagine a case-insensitive index by using a special compare-function for strings:> How do I make a SAPDB case-insensitive?
for example "a" < "A" < "b" < "B" < "c" < "C" etc.
That allows to search for a string "aBc" case-insensitive by outputting all values in the interval ["abc";"ABC"].
The index still allowes search case-sensitive ... because "a"!="A" etc.
Any ideas, why this should not be implemented?
Because there is a better way?
Imagine a string 10 chars long, you'd get 1024 different versions of that by altering case of each letter, what would you do with that? make 1024 queries?
No, the way to handle this is either to accept that it's not very fast and do: select * from foo where upper(bar) = ? or keep an indexed and normalized column around: select * from foo where upper_bar = ?
The upper_bar column would be updated either by a trigger (not very fast in sapdb as it uses only post-triggers, but I may be mistaken :-) ) or by application logic.
This is basicly the same thing you'ld do if you wanted to do indexed searches by soundx values or some other function based index.
It would be cool if sapdb supported function-based indexes internally, so you could say:
CREATE INDEX ucbar ON foo (UPPER(foo(bar)))
or somesuch and have all-uppercase compares on that column automatically use that index, but I guess sap has implemented that in their middle tier to be able to support different databases as this is not a "normal" feature...
I've implemented this in my middle tier as well and it works quite nicely for me, so I for one will not start sulking if sapdb never gets function-based indexes, but hey, it would be cool to have:-)
--
Regards Flemming Frandsen - http://dion.swamp.dk
PartyTicket.Net co founder & Yet Another Perl Hacker
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
