I have a query where I am searching against a string:
SELECT county FROM city WHERE UPPER(name) = 'SAN FRANCISCO';
Now, this works fine, but it doesn't scale well, and I need to optimize it. I have found an option along the lines of creating a generated view, or something like that, but I was hoping for a simpler solution using an index.
We are using DB2, and I really want to use an expression in an index, but this option seems to only be available on z/OS, however we are running Linux. I tried the expression index anyways:
CREATE INDEX city_upper_name_idx
ON city UPPER(name) ALLOW REVERSE SCANS;
But of course, it chokes on the UPPER(name).
Is there another way I can create an index or something similar in this manner such that I don't have to restructure my existing queries to use a new generated view, or alter my existing columns, or any other such intrusive change?
EDIT: I'm open to hearing solutions for other databases... it might carry over to DB2...
Case sensitivity is a characteristic of the field, not the index. Therefore, if an index contains some fields that are case sensitive and some that are not, then the different sorting rules apply. Field names are not case sensitive; they can be uppercase, lowercase, or a combination of both.
SQL Case insensitivity is to use the query statements and the keywords tables and columns by specifying them in capital or small letters of alphabets.
The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default.
Indexes should not be used on small tables. Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. For instance, you would not have an entry for the word "the" or "and" in the index of a book.
You could add an indexed column holding a numerical hash key of the city name. (With duplicates allowed).
Then you could do a multi-clause where :
hash = [compute hash key for 'SAN FRANCISCO']
SELECT county
FROM city
WHERE cityHash = hash
AND UPPER(name) = 'SAN FRANCISCO' ;
Alternatively, go through your db manual and look at the options for creating table indexes. There might be something helpful.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With