Индекс нечувствительности к регистру базы данных?
У меня есть запрос, в котором я ищу строку:
SELECT county FROM city WHERE UPPER(name) = 'SAN FRANCISCO';
Теперь это работает нормально, но плохо масштабируется, и мне нужно его оптимизировать. У меня есть нашел вариант по линиям создания сгенерированного представления или что-то в этом роде, но я надеялся на более простое решение с использованием индекса.
Мы используем DB2, и я действительно хочу использовать выражение в индексе, но эта опция, похоже, доступна только в z / OS, однако мы работаем в Linux. Я все равно попробовал индекс выражения:
CREATE INDEX city_upper_name_idx
ON city UPPER(name) ALLOW REVERSE SCANS;
Но конечно давится ВЕРХНИМ (имя).
Есть ли другой способ создать индекс или что-то подобное таким образом, чтобы мне не приходилось реструктурировать существующие запросы, чтобы использовать новое сгенерированное представление, или изменять мои существующие столбцы, или любое другое подобное навязчивое изменение?
Обновлено: Я готов услышать решения для других баз данных ... они могут быть перенесены в DB2 ...
Ответы 6
Вы можете добавить индексированный столбец, содержащий числовой хеш-ключ названия города. (Допускаются дубликаты).
Тогда вы можете сделать несколько предложений, где:
hash = [compute hash key for 'SAN FRANCISCO']
SELECT county
FROM city
WHERE cityHash = hash
AND UPPER(name) = 'SAN FRANCISCO' ;
В качестве альтернативы просмотрите руководство по БД и посмотрите варианты создания индексов таблиц. Может быть что-нибудь полезное.
Oracle поддерживает индексы на основе функций. Их канонический пример:
create index emp_upper_idx on emp(upper(ename));
PostgreSQL также поддерживает индексацию результатов функции:
CREATE INDEX mytable_lower_col1_idx ON mytable (lower(col1));
Единственный другой вариант, который я могу придумать, - это немного ослабить нормализацию ваших данных, создав еще один столбец для хранения версии в верхнем регистре (обновленной триггерами) и проиндексировать ее. Блеч!
Не нужны триггеры и тому подобное, DB2 поддерживает сгенерированные столбцы.
Я не знаю, будет ли это работать в DB2, но я расскажу, как я бы это сделал в SQL Server. Я считать способ, которым MSSQL делает это, является стандартом ANSI, хотя конкретные строки сопоставления могут отличаться. В любом случае, если вы можете сделать это, не разрушая остальную часть вашего приложения - есть ли другие места, где столбец «имя» должен быть чувствительным к регистру? - попробуйте сделать весь столбец нечувствительным к регистру, изменив параметры сортировки, а затем проиндексируйте столбец.
ALTER TABLE city ALTER COLUMN name nvarchar(200)
COLLATE SQL_Latin1_General_CP1_CI_AS
... где "nvarchar (200)" обозначает любой тип данных вашего текущего столбца. Часть «CI» строки сопоставления - это то, что отмечает ее как нечувствительную к регистру в MSSQL.
Чтобы объяснить ... я понимаю, что индекс будет хранить значения в порядке сортировки индексированного столбца. Если сделать сортировку столбца нечувствительной к регистру, индексное хранилище будет «Сан-Франциско», «САН-ФРАНЦИСКО» и «Сан-Франциско» вместе. Тогда вам просто нужно удалить «UPPER ()» из вашего запроса, и DB2 должна знать, что может использовать ваш индекс.
Опять же, это основано исключительно на том, что я знаю о SQL Server, плюс пара минут просмотра спецификации SQL-92; он может работать или не работать для DB2.
DB2 не силен в отношении сопоставления. И у него нет функциональных индексов.
Предложение Ника Сандерса сработает, если вы согласитесь с тем, что хеширование должно происходить в вашем приложении (поскольку, насколько мне известно, в DB2 нет функций SHA или MD5).
Однако на вашем месте я бы создал материализованное представление (MQT == Materialized Query Table, на языке db2), используя СОЗДАТЬ ТАБЛИЦУ КАК, добавив столбец с предварительно вычисленным вариантом имени в верхнем регистре. Примечание: вы можете добавлять индексы к материализованным представлениям в DB2.
Дешевле как с точки зрения хранения, так и с точки зрения скорости просто добавить еще один сгенерированный столбец в существующую таблицу, а не иметь целую другую таблицу. И я оспариваю, что DB2 слабая с сопоставлением. А, угл твоей сестры ... пардон, увлекся :-).
Короткий ответ, нет.
Длинный ответ, да, если вы работаете на мэйнфрейме, но это не так, поэтому вам придется использовать другие уловки.
DB2 (начиная с DB2 / LUW v8) теперь генерирует столбцы, поэтому вы можете:
CREATE TABLE tbl (
lname VARCHAR(20),
fname VARCHAR(20),
ulname VARCHAR(20) GENERATED ALWAYS AS UPPER(lname)
);
а затем создайте индекс по ulname. Я не уверен, что вы собираетесь сделать это проще, чем это.
Раньше вам приходилось использовать комбинацию триггеров вставки и обновления, чтобы гарантировать синхронизацию столбца ulname, и поддерживать это было кошмаром. Кроме того, теперь, когда эта функциональность является частью базовой СУБД, она сильно оптимизирована (намного быстрее, чем решение на основе триггеров) и не мешает реальным пользовательским триггерам, поэтому нет необходимости поддерживать дополнительные объекты БД.
Подробнее см. здесь.
К сожалению, в DB2 / LUW этого еще нет, но, вероятно, он появится, поскольку в DB2 / z он есть.