Хотелось бы услышать от опытных людей как они решают в каких случаях строить индекс на внешний, а когда - нет.

Пример (вырожденный, для иллюстрации. Не делайте так):

Таблица содержит колонку "пол", который хранится в виде числа 0/1. Эта колонка является внешним ключом на "таблицу полов" обычно содержащую две строчки, как когда-то было с sys.dual.

Таблица полов содержит главный ключ (0,1), который требует индекса как это водится. Таблица полов статична, т.е. тип внешнего ключа не важен. Можно cascade, можно нет.

В этом примере трудно представить полезный запрос, где таблица полов будет ведущей чтобы оправдать наличие индекса пола в таблице людей.

Какими правилами руководствуется вы, решая нужен ли индекс на внешнем ключе, если задумывались об этом раньше?

С первой частью понятно. Вы говорите что наличие внешнего ключа не является фактором при решении нужен индекс или нет.

А что означает полная блокировка таблицы при DML?

Спасибо, этот ответ на мой первоначальный вопрос самый информативный.

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

Брутально. Я почему-то ожидал что только апдейты изменяющие значение колонки ключа вызовут проверку целостности и сопутствующую блокировку.

Очень полезная инфа.

Уже пора засыпать, но в голову лезет такая мысль: а что же происходит с таблицами где одна колонка внешним ключом ссылается на другую?

Как например object_id и parent_id. Я такой давно пользуюсь без индекса на parent_id, это наверное не рекомендуется, ведь такая таблица сама себя может заблокировать.

Завтра обязательно попробую это сделать. Нужно именно delete или update, т.к. insert не должны приводить к перепроверке внешнего ключа, правильно?

Прямо тикающая бомба этот внешний ключ.

"скорость превыше целостности" - олимпийский лозунг

andrey_anonymous,

справедливое замечание.

У меня меньше одного "ляма", поэтому неэффективность решений далеко не всегда очевидна.

10 кратная разница в скорости мало видна, когда почти все в миллисекундах.

Отсюда и многие мои вопросы к господам "дальнобойщикам".
Это у вас боковой ветер и температура воздуха влияет на расход топлива, а я на мотоцикле не замечаю.
Но интересно.