My db background is on the side of MS SQL Server where text comparisons in indexes and restrictions are not case sensitive (at least by default). Therefore, when you have the value "abc" assigned to a unique column, you cannot save the second value "ABC", and if you look for "ABC", SQL Server will find "abc".
Things are different with Oracle, so even with a unique index in the text column, you can store both “abc” and “ABC” there, and if you look for “AbC” you will not get any result.
AFAIK before Oracle 10gR2 there was no way around this, now you can set insensitive comparisons for sesson, which IMHO is not a good solution, because it all depends on the discipline of the programmers.
But the worst case-sensitive case is the one who rewrites all search queries as UPPER(some_column)=UPPER(some_text)(and this is what many discussion topics recommend), ending the table scan, even if some_column has an index. The performance implications are catastrophic: I just checked a simple search in a table with half a million rows, and a search with the UPPER function call took 20 times longer than a search with only the column identifier, thereby confirming that the index is not used when performing the based search function.
Is it true that the most standard method for flawlessly searching the case in an Oracle database is to use the UPPER / LOWER functions to search for items despite poor performance? Or are there more elegant ways to solve this problem?
source
share