У меня есть таблица A
и таблица B
. A
имеет внешний ключ для B
на B
первичный ключ, B_ID
.
По какой-то причине (я знаю, что есть законные причины) он не использует индекс, когда я соединяю эти две таблицы с ключом.
Нужно ли отдельно создавать индекс на A.B_ID
или должно существовать наличие внешнего ключа?
Только ограничение внешнего ключа не предоставляет индекс - нужно (и должно) быть создано.
Создание внешнего ключа автоматически не создает индекс на A.B_ID. Поэтому, как правило, имеет смысл с точки зрения производительности запроса создать отдельный индекс на A.B_ID.
Если вы когда-либо удаляете строки в B, вам определенно нужно индексировать A.B_ID. В противном случае Oracle должен будет выполнить полное сканирование таблицы в при каждом удалении строки из B, чтобы убедиться, что нет сиротских записей (в зависимости от версии Oracle могут быть и дополнительные блокирующие последствия, но они уменьшаются в более поздних версиях Oracle).
B_ID
,
Просто для получения дополнительной информации: Oracle не создает индекс автоматически (как и для уникальных ограничений), потому что (a) не требуется принудительное принуждение, и (b) в некоторых случаях вам не нужен один.
В большинстве случаев вам нужно будет создать индекс (на самом деле, в Oracle Apex есть отчет "неиндексированных внешних ключей" ).
Всякий раз, когда приложение должно удалять строку в родительской таблице или обновлять значение PK (что более редкое), DML будет страдать, если индекс не существует, потому что ему придется заблокировать всю дочернюю таблицу.
В случае, когда я обычно предпочитаю не добавлять индекс, где FK является таблицей "статических данных", которая определяет домен столбца (например, таблицу кодов состояния), где обновления и удаления в родительской таблице никогда не выполняются непосредственно приложением. Однако, если добавление индекса в столбец дает преимущества для важных запросов в приложении, то индекс по-прежнему будет хорошей идеей.
SQL Server никогда не добавлял индексы в столбцы внешнего ключа автоматически - проверьте Kim Tripp отличное сообщение в блоге на фоне и истории этого городского мифа.
Как правило, рекомендуется индексировать столбцы внешнего ключа, но да, я бы рекомендовал убедиться, что каждый столбец FK подкрепляется индексом; не обязательно на одном столбце - возможно, имеет смысл создать индекс на двух или трех столбцах, где столбец FK будет первым. Зависит от вашего сценария и ваших данных.
По соображениям производительности должен быть создан индекс. Используется в операциях удаления в первичной таблице (чтобы проверить, что запись, которую вы удаляете, не используется) и в соединениях, в которых обычно используется внешний ключ. Только несколько таблиц (я их не создаю в журналах) могут быть такими, которые не нуждаются в индексе, но, вероятно, в этом случае, вероятно, вам также не понадобится ограничение внешнего ключа.
НО
Есть несколько баз данных, которые уже автоматически создают индексы для внешних ключей. Jet Engine (Microsoft Access Files) жар-птица MySQL
УВЕРЕНЫ
SQL Server Oracle
НЕ ДЕЛАЕТ
Как и все, что связано с производительностью, оно зависит от многих факторов, и нет ни одной световой пули, например. в условиях очень высокой активности сохранение индекса может быть неприемлемым.
Наиболее существенным здесь будет избирательность: если значения в индексе будут сильно дублированы, то это может дать лучшую производительность, чтобы отбросить индекс (если это возможно) и разрешить сканирование таблицы.
oracle
но это не сразу видно, когда вы попадаете сюда из поиска Google.