Индекс нечувствительный к регистру базы данных?

У меня есть запрос, где я ищу по строке:

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 ...

15.08.2008 22:47:57
6 ОТВЕТОВ

Вы можете добавить индексированный столбец, содержащий числовой хэш-ключ названия города. (С учетом дубликатов).

Тогда вы можете сделать мульти-предложение, где:

hash = [compute hash key for 'SAN FRANCISCO']

SELECT county 
FROM city 
WHERE cityHash = hash 
  AND UPPER(name) = 'SAN FRANCISCO' ;

Кроме того, просмотрите руководство по БД и посмотрите варианты создания табличных индексов. Там может быть что-то полезное.

7
25.08.2017 22:14:45

Oracle поддерживает функционально-ориентированные индексы. Их канонический пример:

 create index emp_upper_idx on emp(upper(ename));  
1
16.08.2008 02:13:32
К сожалению, в DB2 / LUW этого пока нет, но, вероятно, это произойдет, поскольку в DB2 / z оно есть.
paxdiablo 7.01.2009 06:24:09

PostgreSQL также поддерживает индексацию результатов функции:

CREATE INDEX mytable_lower_col1_idx ON mytable (lower(col1));

Единственный другой вариант, о котором я могу подумать, - это немного нормализовать ваши данные, создав еще один столбец для хранения версии в верхнем регистре (обновленной с помощью триггеров) и индексирования ее. Blech!

1
16.08.2008 12:40:25
Не нужны триггеры и тому подобное, DB2 поддерживает сгенерированные столбцы.
paxdiablo 7.01.2009 06:27:33

Я не знаю, будет ли это работать в DB2, но я расскажу вам, как бы я это сделал в SQL Server. Я думаю, что MSSQL делает это стандартом ANSI, хотя конкретные параметры сортировки могут отличаться. В любом случае, если вы можете сделать это, не удаляя остальную часть вашего приложения - есть ли другие места, где столбец «name» должен быть чувствительным к регистру? - попробуйте сделать весь столбец без учета регистра, изменив параметры сортировки, а затем индексируйте столбец.

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.

1
19.08.2008 17:07:22

DB2 не сильна в отношении сортировки. И у него нет индексов на основе функций.

Предложение Ника Сандерса сработает, если вы согласитесь с тем, что хеширование должно происходить в вашем приложении (насколько я знаю, у DB2 нет функций SHA или MD5).

Однако на вашем месте я бы создал материализованное представление (MQT == Materialized Query Table, на языке db2), используя CREATE TABLE AS , добавив столбец с предварительно вычисленным вариантом имени в верхнем регистре. Примечание. Вы можете добавлять индексы к материализованным представлениям в DB2.

1
3.09.2008 23:04:09
Это дешевле с точки зрения хранения и скорости, просто добавить еще один сгенерированный столбец в существующую таблицу, а не иметь целую другую таблицу. И я оспариваю, что DB2 слаба в сопоставлении. И, твоя сестра уродливая ... извини, увлеклась :-).
paxdiablo 7.01.2009 06:26:46

Короткий ответ, нет.

Длинный ответ: да, если вы работаете на мэйнфрейме, но это не так, вам придется использовать другие хитрости.

DB2 (начиная с DB2 / LUW v8) теперь генерирует столбцы, поэтому вы можете:

CREATE TABLE tbl (
    lname  VARCHAR(20),
    fname  VARCHAR(20),
    ulname VARCHAR(20) GENERATED ALWAYS AS UPPER(lname)
);

а затем создать индекс на ulname. Я не уверен, что вы поймете это проще, чем это.

До этого вам приходилось использовать комбинацию триггеров вставки и обновления, чтобы обеспечить синхронизацию столбца ulname, и это было кошмаром. Кроме того, теперь, когда эта функциональность является частью базовой СУБД, она сильно оптимизирована (она намного быстрее, чем решение на основе триггеров) и не мешает действительным пользовательским триггерам, поэтому никаких дополнительных объектов БД для обслуживания не требуется.

Смотрите здесь для деталей.

5
7.01.2009 06:22:22