Внешние ключи нужно индексировать.
Не проиндексированные внешние ключи являются, как правило, наиболее частой причиной возникновения взаимных блокировок, поскольку изменение в главной таблице ( UPDATE по всем столбцам строки, такие апдейты любят делать средства автоматической генерации SQL) или удаление записи из главной таблицы приводит в этом случае к блокированию всей подчиненной таблицы (никакие изменения в таблице внешнего ключа будут невозможны, пока транзакция не завершится). При этом блокируется намного больше строк, чем нужно, и снижается параллелизм.
Не проиндексированный внешний ключ плох еще и в следующих случаях:
- При наличии конструкции ON DELETE CASCADE. Например, таблица ЕМР является подчиненной для таблицы DEPT. Оператор DELETE FROM DEPT WHERE DEPTNO = 10 должен вызвать каскадное удаление в таблице ЕМР. Если столбец DEPTNO в таблице ЕМР не проиндексирован, для этого придется выполнить полный просмотр таблицы ЕМР. Этот полный просмотр нежелателен; кроме того, при удалении большого количества строк из главной таблицы подчиненная будет каждый раз полностью просматриваться.
- При выполнении запроса от главной таблицы к подчиненной. Рассмотрим пример с таблицами EMP/DEPT еще раз. Очень часто таблица ЕМР запрашивается с условием по столбцу DEPTNO. Если приходится часто выполнять запрос:
Когда не нужно индексировать внешний ключ:
- данные из главной таблицы не удаляются;
- значение первичного/уникального ключа главной таблицы не изменяется ни намеренно, ни случайно (используемым инструментальным средством);
- не выполняется соединение от главной таблицы к подчиненной, т.е. столбцы внешнего ключа не обеспечивают важный способ доступа к подчиненной таблице (как в случае таблиц DEPT и ЕМР).
Выявить блокировку из-за не проиндексированного внешнего ключа
Если предполагается, что подчиненная таблица блокируется из-за того, что не проиндексирован внешний ключ и необходимо в этом убедиться (или предотвратить), можно выполнить команду:
ALTER TABLE <имя подчиненной таблицы> DISABLE TABLE LOCK;
Теперь оператор UPDATE или DELETE, примененный к главной таблице и вызывающий блокирование подчиненной таблицы, приводит к выдаче сообщения:
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for <имя подчиненной таблицы>
Не проиндексированные внешние ключи являются, как правило, наиболее частой причиной возникновения взаимных блокировок, поскольку изменение в главной таблице ( UPDATE по всем столбцам строки, такие апдейты любят делать средства автоматической генерации SQL) или удаление записи из главной таблицы приводит в этом случае к блокированию всей подчиненной таблицы (никакие изменения в таблице внешнего ключа будут невозможны, пока транзакция не завершится). При этом блокируется намного больше строк, чем нужно, и снижается параллелизм.
Не проиндексированный внешний ключ плох еще и в следующих случаях:
- При наличии конструкции ON DELETE CASCADE. Например, таблица ЕМР является подчиненной для таблицы DEPT. Оператор DELETE FROM DEPT WHERE DEPTNO = 10 должен вызвать каскадное удаление в таблице ЕМР. Если столбец DEPTNO в таблице ЕМР не проиндексирован, для этого придется выполнить полный просмотр таблицы ЕМР. Этот полный просмотр нежелателен; кроме того, при удалении большого количества строк из главной таблицы подчиненная будет каждый раз полностью просматриваться.
- При выполнении запроса от главной таблицы к подчиненной. Рассмотрим пример с таблицами EMP/DEPT еще раз. Очень часто таблица ЕМР запрашивается с условием по столбцу DEPTNO. Если приходится часто выполнять запрос:
select * from dept, emp
where emp.deptno = dept.deptno
and dept.dname = :X;
для генерации отчета или других целей, окажется, что отсутствие индекса существенно замедляет выполнение запросов.where emp.deptno = dept.deptno
and dept.dname = :X;
Когда не нужно индексировать внешний ключ:
- данные из главной таблицы не удаляются;
- значение первичного/уникального ключа главной таблицы не изменяется ни намеренно, ни случайно (используемым инструментальным средством);
- не выполняется соединение от главной таблицы к подчиненной, т.е. столбцы внешнего ключа не обеспечивают важный способ доступа к подчиненной таблице (как в случае таблиц DEPT и ЕМР).
Выявить блокировку из-за не проиндексированного внешнего ключа
Если предполагается, что подчиненная таблица блокируется из-за того, что не проиндексирован внешний ключ и необходимо в этом убедиться (или предотвратить), можно выполнить команду:
ALTER TABLE <имя подчиненной таблицы> DISABLE TABLE LOCK;
Теперь оператор UPDATE или DELETE, примененный к главной таблице и вызывающий блокирование подчиненной таблицы, приводит к выдаче сообщения:
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for <имя подчиненной таблицы>
