too many keys defined for index

[Brainenjii]

Пытаюсь организовать OLAP.
Для хранилища создал табличку с достаточно большим числом полей - таблицам измерений. Решил, что лучшим выбором будет создать составной индекс на все эти поля. Но начиная где-то с 18 - вылезает сабжевая ошибка.
Как лучше поступить? Разбить большой индекс на несколько меньших? Насколько быстродейственно будет такая система?
В запросах к таблице-хранилищу планируется всегда указывать все таблицы-измерения в качестве условий.

[Dimitry Sibiryakov]

Число сегментов составного индекса ограничено 16-ю.

[kdv]

не вижу смысла создавать индекс более чем на 2-3 столбца. Более того, такой индекс имеет смысл только если существуют запросы с поиском на равенство по N-1 столбцов в индексе. В противном случае лучше просто на те столбцы, по которым предполагается "фильтрация" в where, создать отдельные индексы (по одному на столбец).

Решил, что лучшим выбором будет создать составной индекс на все эти поля.

Решение, очевидно, было принято при полном непонимании, как работает оптимизатор (в любой СУБД), и как можно искать по диапазонам в любом b-дереве.

Если есть столбцы A и B, и Вы создаете индекс по A+B, то оптимизатор будет использовать индекс только если в where указано либо A, либо A и B. По только B такой индекс использован быть не может, потому что это элементарно. Ключи в составном индексе отсортированы от первого к последнему столбцу. Например
A B
1 1
1 2
1 3
2 1
2 2
2 3

Как видите, в столбце B ключи идут не "подряд". Т.е. не 1, 1, 2, 2, 3, 3, а группами, в соответствии с ключами в столбце A. Как бы, это азы.

[Brainenjii]

Хм... Насколько я вижу решение задачи сейчас - мне нужно создать таблицу фактов с полями из таблиц-справочников. Т.е., условно:

Код: Выделить всё

CREATE TABLE DIM_1 (ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL, CAPTION VARCHAR(512));
CREATE TABLE DIM_2 (ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL, CAPTION VARCHAR(512));
CREATE TABLE DIM_3 (ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL, CAPTION VARCHAR(512));
CREATE TABLE FACTS(
    ID INTEGER NOT NULL, 
    DIM1 INTEGER NOT NULL, 
    DIM2 INTEGER NOT NULL,
    DIM3 INTEGER NOT NULL, 
    FACT_VALUE INTEGER NOT NULL);

И тогда, для получения FACT_VALUE я всегда буду указывать все DIMX:

Код: Выделить всё

SELECT ID, FACT_VALUE FROM FACTS 
WHERE DIM1 = 1 AND DIM2 IN (2, 5, 8) AND DIM3 BETWEEN 100 AND 200;

Вот, как я уже говорил - всегда все DIMx в WHERE будут указаны. Очерёдность, кстати, роли вроде как особо не играет - по-крайней мере FlameRobin выдаёт использование индекса в плане вне зависимости от встречи DIM в условии.
Ну да ладно, смысла в этом действительно теперь никакого нет, раз ограничен максимум полей для составного индекса. Буду создать по индексу на каждое поле-измерение. Правда их может быть достаточно много (пока максимум 57). И когда-то давно встречал в статьях отрицательные отзывы об использовании неуникальных индексов и старался обходиться без них. Мне стоит перечитать документацию и всё не так плохо, или всё изменилось?

[kdv]

Вот, как я уже говорил - всегда все DIMx в WHERE будут указаны. Очерёдность, кстати, роли вроде как особо не играет - по-крайней мере FlameRobin выдаёт использование индекса в плане вне зависимости от встречи DIM в условии.

Вы мои ответы читаете, или пришли сюда со своими монологами?

WHERE DIM1 = 1 AND DIM2 IN (2, 5, AND DIM3 BETWEEN 100 AND 200;

индекс по DIM1+DIM2+DIM3 подхватится только для 1-го столбца. Остальные столбцы для данного поиска через индекс использованы быть не могут. Попытайтесь еще раз прочитать мой предыдущий ответ.

кстати, DIM2 IN (2,5,8) это на самом деле (DIM2 = 2) OR (DIM2=5) OR (DIM2=8). Так что даже одиночный индекс по DIM2 лучше не использовать. Так что по всем 57 столбцам индексы лучше сразу не создавать.

И когда-то давно встречал в статьях отрицательные отзывы об использовании неуникальных индексов и старался обходиться без них.

"когда-то где-то что-то..." Где отрицательные отзывы? О чем именно? Отрицательные о неуникальных индексах, и положительные об уникальных?
"Старался обходиться без неуникальных индексов" - Вы это серьезно?

[Brainenjii]

Перечитал. Потом ещё статью про методы доступа. В ней сказано:

...для композитного индекса по полям (A, B, C) следует, что:
он может быть использовать для предикатов (A = 0) или (A = 0 and B = 0) или (A = 0 and B = 0 and C = 0), но не может быть использован для предикатов (B = 0) или (C = 0) или (B = 0 and C = 0);
предикат (A = 0 and B > 0 and C = 0) приведет к частичному совпадению по двум сегментам, а предикат (A > 0 and B = 0) - к частичному совпадению всего по одному сегменту.

Тоже самое, что у Вас, но ещё с полем C. И полностью как в моей задаче. Повторюсь - в моём случае выборок без учёта DIM1 (равно как и всех остальных полей-измерений) не будет. Всегда во всех запросах должно происходить полное совпадение. Правда составной ключ мне все-равно не подходит.
По неуникальным индексам, простой поиск в гугле приводит сразу несколько тех самых отрицательных отзывов. Правда когда я читал те статьи - там ещё не было поправок на 2.0, вроде как... В общем, давным-давно сложились предубеждения по неуникальным индексам, и ни разу их не создавал.
В общем... Я так понимаю, что для реализации моей задачи (хранилище данных, организованное по принципам OLAP) подойдут неуникальные индексы, созданные по некоторым, наиболее предпочтительным измерениям?
Или же всё-таки создать множество составных индексов? Таблицы-измерения в хранилище фактов будут представлены собственно измерением и 2-8 полями для фильтрации значений. Т.е., к примеру, таблица со структурой организаций, и таблица с видами деятельности, системами налогообложения и прочими группировками. Каждая организация может заниматься сразу несколькими видами деятельности и обладает какой-то системой налогообложения. Т.о. в таблице-факте одно измерение на самом деле будет представлено сразу несколькими полями. Условно:

Код: Выделить всё

CREATE TABLE FACTS(
    ID INTEGER NOT NULL, 
    DIM1 INTEGER NOT NULL, 
    DIM1_FIL1 INTEGER NOT NULL,
    DIM1_FIL2 INTEGER NOT NULL,
    DIM2 INTEGER NOT NULL,
    DIM2_FIL3 INTEGER NOT NULL,
    DIM2_FIL4 INTEGER NOT NULL,
    DIM2_FIL5 INTEGER NOT NULL,
    DIM3 INTEGER NOT NULL, 
    DIM3_FIL6 INTEGER NOT NULL,
    FACT_VALUE INTEGER NOT NULL);

Возможно стоит создавать композитные индексы по количеству измерений? В первый объединить (DIM1, DIM1_FIL1, DIM1_FIL2), во второй (DIM2, DIM2_FIL3, DIM2_FIL4, DIM2_FIL5) и т.д. Время записи в таблицу фактов не критично (в разумных пределах). Или я слишком радужно вижу ситуацию и стоит ещё читать и читать?

[kdv]

По неуникальным индексам, простой поиск в гугле приводит сразу несколько тех самых отрицательных отзывов

допустим, но с тормозами при удалении мусора в индексах уже разобрались в ФБ 2, а кроме того, индексы сами по себе быстрее начиная с ФБ 2.1.
http://www.ibase.ru/devinfo/rs-win-se-ind.gif
http://www.ibase.ru/devinfo/restorespeed.htm

Правда когда я читал те статьи - там ещё не было поправок на 2.0, вроде как...

вот именно. А статья delmany вообще была написана во времена царя гороха. Диски нынче раз в 10 быстрее как минимум.

В общем, давным-давно сложились предубеждения по неуникальным индексам, и ни разу их не создавал.

предубеждение, основанное на неверном понимании информации. Неуникальный индекс - это индекс, не контролирующий уникальность ключей.
Что Вы туда запихнете - ваше дело. Можете туда запихнуть миллионы одинаковых значений, и будет плохо. Можете туда запихнуть десять тысяч разных значений на миллион записей - и будет хорошо. Так что здесь все относительно.

Я так понимаю, что для реализации моей задачи (хранилище данных, организованное по принципам OLAP) подойдут неуникальные индексы, созданные по некоторым, наиболее предпочтительным измерениям?

именно так. Причем нужно просто посмотреть, если где-то будут совсем неуникальные значения, типа, 2-3 на миллион, то да, по такому столбцу индекс строить не надо.

Или же всё-таки создать множество составных индексов?

о боже я же Вам показал, что даже в Вашем примере составной индекс по факту не работает, т.е. он используется только по столбцу DIM1. И Вы правильно привели цитату из dataaccesspaths, но разве у Вас все сегменты индекса (кроме последнего) будут проверяться на равенство?

В первый объединить (DIM1, DIM1_FIL1, DIM1_FIL2), во второй (DIM2, DIM2_FIL3, DIM2_FIL4, DIM2_FIL5) и т.д.

дальше-то что делать с этими индексами? Ваши запросы всегда будут искать по
where DIM1 = X and DIM1_FIL1 = Y and DIM1_FIL2 > Z ?

Вы как-то упорно не хотите представить себе, как будут храниться данные в Ваших композитных индексах

Более того. Например, если столбец DIM1 имеет уникальность значений выше, чем остальные столбцы, то тогда вообще даже при where по трем столбцам может хватить индекса только по столбцу DIM1.
Допустим,
where DIM1 = X and DIM1_FIL1 = Y
отбирает 10 записей, которые являются пересечением 15-ти записей по индексу DIM1 и 100 записей по индексу DIM1_FIL1. Композит по этим двум столбцам, конечно, выдаст информацию быстрее, чем один индекс по DIM1, но даже если будет только индекс по DIM1, сервер выберет 15 записей, и лишние 5 отсечет по условию DIM1_FIL1 = Y.

Так что при создании индексов нужно смотреть на данные, и на ИХ селективность. А композитными индексами пользоваться только если есть "фиксированные" запросы, которые всегда в where имеют четкий перечень столбцов с четкими условиями.