Dmitriy PopovПомнится как-то я задавал вопрос про то как сделать поле с внешним ключом уникальным с минимальными потерями. См тут.

Вопрос возник из нежелания создавать уникальный индекс по уже проиндексированному полю внешнего ключа. Я создавал CHECK, но скрипт выглядел достаточно по-идиотски из-за асобенностей ASA интерпретации этого самого CHECK\'а.

Так вот, таких таблиц стало становиться больше и больше, поэтому решил потестировать как все же эффективнее, чтобы так везде потом делать.

Докладываю:

Соэдал 2 идентичные таблицы. Таблицу t1 с доп. уникальным индексом, таблица t2 его не имела и пользовалась CHECK\'ом по полю внешнего ключа. Была таблица с 2000 записей (покрупнее не получилось нарыть) из которой я их "загружал". Операции с наборами записей работали быстро с разницей в сотые секунды, поэтому написал скриптик, который вставлял записи по одной (чтоб ему сложней было переиндексировать индекс / перепроверять CHECK с EXISTS() после каждого раза).

Чтобы исключить влияние кеширования, тесты проводил так: три раза подряд на таблице 2, потом 3 раза подряд на таблице 1 (тесты 1-3), потом то же самое, только по 2 раза (тесты 4-5). Время в таблице в секундах, выдаваемое ISQL в окне сообщений.


Также было замечено что DELETE FROM с пустым WHERE работал стабильно 0.51 - 0.53 с на первой таблице, и 0.37 - 0.39 на второй. Неудивительно, ибо при удалении записи индекс надо "подправлять", а CHECK не надо - она ж удаляется.

Выводы:
1. CHECK не тормозит
2. CHECK быстрее.

может кому сгодится...
Вы ради интереса создайте не 2000 записей, которые в кэш помещаются, а 2 миллиона и снова проведите тест. Причем не с одной сессией, а так с десяток. Окажется, что CHECK будет для проверки уникальности на каждую вставляемую запись каждый раз выполнять запрос, а так как первичный ключ на таблицу составной, то он будет эту несчастную таблицу каждый раз сканить физически. А вот по уникальному индексу или CONSTRAINT ему будет достаточно спуститься по дереву индекса до нода, большего вставляемой записи и успокоится. Так же окажется, что при одновременной работе множества сессий при CHECK оптимизатор будет блокировать записи (как - это зависит от уровня установленной для сессии изоляции) и параллейно вставляемые записи из разных сессий будут здорово друг другу мешаться. Плюс если у сессий долгая транзакция (то есть до COMMIT выполняются другие операции), то параллейность вообще при Вашем способе изчезнет, так как следующая же сессия при вставке записи на запросе уткнется на блокированную запись и будет ждать, пока ее не разблокируют. Если же у Вас включен режим изоляции грязного чтения, то вполне возможен вариант, что 2 сессии сваляют дурака и вставят неуникальные значения. Думаю если поднапрячься, можно придумать еще сотню-другую аргументов, почему реализованная ручками проверка на уникальность будет не просто уступать, а вообще не работать, по сравнению с гарантированным механизмом обеспечения уникальности, надо думать не зря прописанным в стандарте ANSI SQL и поддерживаемом во всех РСУБД. Так что выводы Ваши только могут сбить с толку начинающих и доставить им большую кучу проблем, я еще раз рекомендую пользоваться стандартными возможностями, не изобретать велосипедов и читать RTFM.

P.S. Самое что я не могу понять - почему Вы не хотите воспользоваться стандартным штатным механизмом, стоят же уникальные индексы на таблицах с миллионными записями по всем БД в мире и ничего - вроде никто не жалуется и проблем не испытывает. Или хотите пару десятков килобайт размера БД на табличке с 2-мя тысячами записей сэкономить и огрести пару десятков проблем ? Это абсолютно неверный подход при изучении продукта, поверьте моему опыту - прежде чем говорить, что это неправильно и изобретать свое решение однозначно нужно полностью изучить и довольно долго проработать с продуктом, даже если Вам это будет казаться неправильным и нелогичным. Это применимо абсолютно к любому ПО.

Кстати вдогонку - на форуме по развитию ASA разработчикам писали об необходимости возможности отключения автоматического создания индексов на FOREIGN KEY. Так что вполне возможно, что в ASA 9.02 или ASA 10 таких вопросов возникать уже не будет. Пока же штатные средства и только штатные средства везде где можно, особенно по всему, что касается целостности данных.

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

MasterZivДля того, чтобы сделать какие-то сочетания полей таблицы уникальными, надо сделать UNIQUE CONSTRAINT на эти поля, или уникальный индекс. Если это реализовать с помошью CHECK CONSTRAINT с запросом, или с помощью триггера, то все равно придется создавать индекс для того, чтобы процверяющий запрос в триггере или констрейнте работал быстро на большой таблице. Так что не фиг огород городить - пользуйся стандартными средствами.
В ASA на FOREIGN KEY автоматически создаются индексы. Соотвествующе если бы поле FOREIGN KEY хотелось сделать уникальным, приходиться делать еще один индекс, то есть индекс по FOREIGN KEY будет просто висеть мертвым грузом, так как оптимизатор всегда предпочтет воспользоваться уникальным. Изменить или убить такой автоматически создаваемый индекс возможности нет, на что и сетовал автор. Но по любому я считаю, что заниматься велосепидизмом будет еще хуже - насчет возможности опционального автоматического создания таких индексов разработчикам заявлено, остается только ждать и пока тащить пару индексов. Или же просто не устанавливать FOREIGN KEY и реализовать ручками на триггерах проверку целостности и при необходимости каскады с реализацией оптимальной блокировки родительских записей на SELECT посредством хинта XLOCK, оставив только уникальный индекс (что я бы и сделал, если уж очень табличка большая была и действительно 2 индекса держать в ней было бы жирновато). Но вот приколов с CHECK я понять не могу, честное слово.

авторТо, что отработка CHECK'a на каждую запись проходит медленно еще не факт, они постоянно что-нибудь по-новому кэшируют-оптимизируют. Да и почему Вы решили что на каждую - может они с множеством записей работают? Как он там внутри работает никто ведь не расскажет.
Расскажет :) "MESSAGE TO CLIENT" все расскажет о внутренних механизмах ASA. Я обычно чтобы посмотреть, что и в каком порядке выполняется, пишу функцию, в которой посылаю на клиента сообщение и использую ее в нужных местах. Именно таким образом я вычислил, что блокировка для каждой записи происходит непосредственно после выполнения BEFORE триггера, что CHECK вызывается для каждой записи, что при уровне изоляции не serializable в том же table1 UNION ALL table1 можно получить гораздо больше записей, если другая сессия их добавляет во время выполнения этого запроса и т.д. В общем можно сказать, что благодаря тому, что у ASA большая функциональность, простор для исследований ее внутренних механизмов работы имеется неплохой :)

E-docКхе... или я что-то не понял, или тут задача какая-то странная. Разве в этом случае не получается жесткая связь "один-к-одному"? А нужна ли тогда вторая таблица, может все в первую уляжется нормально? И, с другой стороны, кто мешает вам столбец a_id в таблице b объявить как UNIQUE? Вот вам и будет обеспечение уникальности.
Второй вариант, в случае если на таблицу "a" и таблицу "b" еще кто то ссылается - это сделать 3-ю таблицу "c" с 2-мя ключевыми полями a_id и b_id. В итоге будет 2 независимых сущности "дворники" и "лопаты", плюс таблица, которая показывает их соединение (в которую кстати если еще вставить поля open_date и close_date, то можно обеспечить хранение истории перемещения лопат между дворниками).

Dmitriy Popov
Хорошо.

Есть две таблицы

CREATE TABLE a (a_id bigint primary key, MyData char(10))

CREATE TABLE b (
b_id bigint PRIMARY KEY,
a_id bigint REFERENCES a(a_id)
MyData char(10))

Требуется, чтобы только одна запись в b могла ссылаться на запись в a. Ну типа есть дворники и лопаты, и только один дворник может работать этой лопатой в настоящий момент.
ASA сразу создает индекс на a_id в таблице b, т.к. это внешний ключ. Чтобы сделать поле уникальным, приходится вешать уникальный индекс, но тогда, как уже объяснял ASCRUS индекс, созданный сервером болтается просто так и на его перестроение только лишнее время тратится.
IMHO согласен с E-doc - необходима не борьба с индексами, а пересмотр проектирования этой части схемы БД, так как Вы действительно пытаетесь добиться связи один-к-одному в данном случае не понятно зачем.

Все понятно - лопата может валяться в сарае без дворника, потому что он уволился, а нового не нашли. Делайте 3-ю таблицу и будет у Вас все работать.